问题描述
- 把多表联查的sql语句转换成linq语句
-
SELECT * FROM [group_product]
where proid in(select proid from [group_browsehistory]
where memid in(select memid from [group_browsehistory] where proid=12))在线等，这个sql语句怎么转换成linq？？？
解决方案
from x in group_product
where group_browsehistory.Select(y => y.proid).Where(z => group_browsehistory.Where(v => v.proif == 12).Select(w => w.memid).Contains(z)).Contains(x,proid)
解决方案二：
x,proid
->
x.proid
解决方案三：
最好加上top 和 order by 如下：
SELECT top(2)* FROM [group_product]
where proid in(select proid from [group_browsehistory]
where memid in(select memid from [group_browsehistory] where proid=12))
order by apptime desc
我是想获取浏览过proid=12的商品的人浏览过的其他商品。
解决方案四：
谢谢caozhy
可是有报错
怎么弄？
解决方案五：
多表联合查询SQL语句
sql语句一例：多表联合查询
SQL语句多表查询
解决方案六：
@caozhy 谢谢啦！ 现在不报错了，调试成功，即来结贴！
解决方案七：
生成的语句错的........ 以下是生成的sql语句，把各查询字段改成了* 以便查看:
SELECT *
FROM [dbo].[group_product] AS [t0]
WHERE EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[group_browsehistory] AS [t1]
WHERE ([t1].[proid] = [t0].[proid]) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[group_browsehistory] AS [t2]
WHERE ([t2].[memid] = [t1].[proid]) AND ([t2].[proid] = 12)
))
)
查询出来是空的......
怎么办？
解决方案八：
谢谢大家~已经解决啦，分成了2个语句：
//用户
var users = (from o in db.Group_browsehistory
where o.Proid == proid
group o by o.Memid into br
select new
{
Key = br.Key,
Count = br.Count()
}).ToList();
List<int> ids = users.Select(o => o.Key).ToList();
var q = (
from b in db.Group_browsehistory
where ids.Contains(b.Memid)
group b by b.Proid into pr
join p in db.Group_product on pr.Key equals p.Proid
select new GProduct
{
Proid = p.Proid,
Name = p.Name,
Mainpic = p.Mainpic,
Userprice = p.Userprice,
Unuserprice = p.Unuserprice,
Shopprice = p.Shopprice
}).Distinct();
var list = q.Take(count).ToList();
解决方案九：
额..............这个该怎么结贴？？