深夜求救,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(里面不允许查询条件?只能固定参数?)
...全文
378 21 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
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里面的条件没执行,没有用

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧