深夜求救，sql关联查询

BigGreenHand 2015-11-19 11:13:46

SELECT [学号][班级]
FROM [学生表] AS a
INNER JOIN [班级表] AS b
ON a.[班级ID] = b.[班级ID]
WHERE a.[学号] = 200401 最后带个固定参数没问题，但是假如我后面不是固定参数呢

SELECT [学号][班级]
FROM [学生表] AS a
INNER JOIN [班级表] AS b
ON a.[班级ID] = b.[班级ID]
WHERE a.[学号] IN (SELECT c.[学号] FROM c WHERE a.[xx]=c.[xx]) 难道是JOIN ON WHERE（里面不允许查询条件？只能固定参数？）

...全文

335 21 1 打赏收藏举报

写回复

21 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

不穿内裤的超人 2015-11-20

with cte1(id,value) as 
(select 1,123 union all select 2,1654 union all select 3,3542),
 cte2(id,zs) as 
(select 1,'dfhf' union all select 2,'sdfjytu' union all select 4,'sdfsdgdgd')
select * from cte1 join cte2 on cte1.id=cte2.id
where cte1.id in (select id from cte1 where cte1.id<>cte2.id)

打赏
举报

回复赞

不穿内裤的超人 2015-11-20

SELECT [学号][班级] FROM [学生表] AS a INNER JOIN [班级表] AS b ON a.[班级ID] = b.[班级ID] WHERE a.[学号] IN (SELECT c.[学号] FROM c WHERE a.[xx]=c.[xx]) in 里面怎么带入a表的？这样写不对啊，应该改一下就好了

打赏
举报

回复赞

不穿内裤的超人 2015-11-20

这是报错呢还是结果不对？

打赏
举报

回复赞

qq_32010321 2015-11-20

引用 12 楼 BigGreenHand的回复:

SELECT 姓名,SUM(总分) AS 总分 FROM( SELECT UserName AS 姓名,COUNT(UserName)AS 总分 FROM Comm_MyCollection t1 LEFT JOIN Comm_User t2 ON t1.UserID=t2.ID WHERE t2.ZZJG IN (SELECT JobID FROM Sys_Job WHERE MyFather =@MyFather) AND t1.WhereCollect='Comm_Article' AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate GROUP BY t2.UserName UNION SELECT UserName AS 姓名,COUNT(UserName)AS 总分 FROM Comm_MyCollection t1 LEFT JOIN Comm_User t2 ON t1.UserID=t2.ID WHERE t2.ZZJG IN (SELECT JobID FROM Sys_Job WHERE MyFather =@MyFather) AND t1.WhereCollect='Course_Base' AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate GROUP BY t2.UserName )a group by 姓名 ORDER BY SUM(总分) DESC 是这个大神

好像少了个括号吧…………

打赏
举报

回复赞

BigGreenHand 2015-11-20

这样吧，SELECT t2.UserName AS 姓名,COUNT(1)AS 总分 FROM Comm_MyCollection t1 INNER JOIN Comm_User t2 ON t1.UserID=t2.ID WHERE t2.ZZJG IN (数组变量) AND t1.WhereCollect IN('Comm_Article','Course_Base') AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate GROUP BY t2.UserName ORDER BY 总分 DESC IN 里面是数组变量，SQL有没有这个功能？

打赏
举报

回复赞

BigGreenHand 2015-11-20

写法是没有问题，业务逻辑跟数据肯定是没有错的单单IN (SELECT JobID FROM Sys_Job WHERE MyFather =@MyFather) 执行这一句是有结果集的但是放入总的里面就执行不到这一句，假如我 SELECT t2.UserName AS 姓名,COUNT(1)AS 总分 FROM Comm_MyCollection t1 INNER JOIN Comm_User t2 ON t1.UserID=t2.ID WHERE t2.ZZJG IN (13,14,15) AND t1.WhereCollect IN('Comm_Article','Course_Base') AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate GROUP BY t2.UserName ORDER BY 总分 DESC 这样就有数据

打赏
举报

回复赞

等不到来世 2015-11-20

写法本身没有问题。如果还得不到期望结果，那么说明你的业务逻辑或者数据有错误。

打赏
举报

回复赞

BigGreenHand 2015-11-20

IN (SELECT JobID FROM Sys_Job WHERE MyFather =@MyFather) 这里的条件没出来。没用

打赏
举报

回复赞

等不到来世 2015-11-20

SELECT t2.UserName AS 姓名,COUNT(1)AS 总分 
FROM Comm_MyCollection t1 
INNER JOIN Comm_User t2 ON t1.UserID=t2.ID 
WHERE t2.ZZJG IN (SELECT JobID FROM Sys_Job WHERE MyFather =@MyFather)   
AND t1.WhereCollect IN('Comm_Article','Course_Base') AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate 
GROUP BY t2.UserName 
ORDER BY 总分 DESC

打赏
举报

回复赞

等不到来世 2015-11-20

语句没啥问题，就是太繁琐了，等价于下面这条。你具体说说那条数据没查出来？

SELECT t2UserName AS 姓名,COUNT(1)AS 总分 
FROM Comm_MyCollection t1 
INNER JOIN Comm_User t2 ON t1.UserID=t2.ID 
WHERE t2.ZZJG IN (SELECT JobID FROM Sys_Job WHERE MyFather =@MyFather)   
AND t1.WhereCollect IN('Comm_Article','Course_Base') AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate 
GROUP BY t2.UserName 
ORDER BY 总分 DESC

打赏
举报

回复赞

BigGreenHand 2015-11-20

@szx1999 大神，是下面那个，我的F5已按烂，坐等

打赏
举报

回复赞

BigGreenHand 2015-11-20

SELECT 姓名,SUM(总分) AS 总分 FROM( SELECT UserName AS 姓名,COUNT(UserName)AS 总分 FROM Comm_MyCollection t1 LEFT JOIN Comm_User t2 ON t1.UserID=t2.ID WHERE t2.ZZJG IN (SELECT JobID FROM Sys_Job WHERE MyFather =@MyFather) AND t1.WhereCollect='Comm_Article' AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate GROUP BY t2.UserName UNION SELECT UserName AS 姓名,COUNT(UserName)AS 总分 FROM Comm_MyCollection t1 LEFT JOIN Comm_User t2 ON t1.UserID=t2.ID WHERE t2.ZZJG IN (SELECT JobID FROM Sys_Job WHERE MyFather =@MyFather) AND t1.WhereCollect='Course_Base' AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate GROUP BY t2.UserName )a group by 姓名 ORDER BY SUM(总分) DESC 是这个大神

打赏
举报

回复赞

BigGreenHand 2015-11-20

SELECT 姓名,SUM(总分) AS 总分 FROM( SELECT UserName AS 姓名,COUNT(UserName)AS 总分 FROM Comm_MyCollection t1 LEFT JOIN Comm_User t2 ON t1.UserID=t2.ID WHERE t2.ID IN (SELECT t2.ID FROM Comm_User WHERE ZZJG =450001000000) AND t1.WhereCollect='Comm_Article' AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate GROUP BY t2.UserName UNION SELECT UserName AS 姓名,COUNT(UserName)AS 总分 FROM Comm_MyCollection t1 LEFT JOIN Comm_User t2 ON t1.UserID=t2.ID WHERE t2.ZZJG IN (SELECT t2.ID FROM Comm_User WHERE ZZJG =450001000000) AND t1.WhereCollect='Course_Base' AND CreateTS-1>=@startdate AND CreateTS-1<=@enddate GROUP BY t2.UserName )a group by 姓名 ORDER BY SUM(总分) DESC

打赏
举报

回复赞

等不到来世 2015-11-20

你把原始语句贴出来，我帮你改

打赏
举报

回复赞

BigGreenHand 2015-11-20

即使不是别的表，只要IN 后面是查询语句，不是固定参数，一样也不行，我就纳闷

打赏
举报

回复赞

等不到来世 2015-11-20

表别名 <> 别的表名

打赏
举报

回复赞

BigGreenHand 2015-11-20

那如果IN里面的子查询一定是别的表名呢？有何方法？

打赏
举报

回复赞

等不到来世 2015-11-20

查询没什么问题，但是IN里面的子查询使用的表别名不能乱用，尽量使用和外面不同的表别名。

打赏
举报

回复赞

BigGreenHand 2015-11-20

没人吗？不是吧，这个东西应该很常见吧，，我只是想 SELECT [学号][班级] FROM [学生表] AS a INNER JOIN [班级表] AS b ON a.[班级ID] = b.[班级ID] WHERE a.[学号] IN (这里是select 查询语句，不是固定参数，查出来的值也正确，但是就是不执行)