深夜求救,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(里面不允许查询条件?只能固定参数?)
...全文
332 1 收藏 21
写回复
21 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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)
回复
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表的?这样写不对啊,应该改一下就好了
回复
这是报错呢还是结果不对?
回复
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 查询语句,不是固定参数,查出来的值也正确,但是就是不执行)
回复
BigGreenHand 2015-11-20
没报错,就是where里面的条件没执行,没有用
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-11-19 11:13
社区公告
暂无公告