寻求一sql解决方案
1
最佳答案
0
是子查主的问题，通过子表中name=a且name=c的订单 通过orderid关联查询订单主表，我的SQL语句如下
select g.orderid,g.name2 from (select e.orderid from (select a.id,a.[name],a.orderid from #t2 as a where a.name='a') as d inner join (select a.id,a.[name],a.orderid from #t2 as a where a.name='c') as e on e.orderid=d.orderid) as f left join #t1 as g on g.orderid=f.orderid
收获园豆：25
其他回答(5)
0
name=a 并且 name=c 能查出数据？
收获园豆：5
楼主说出了，应该是“或”吧
@会长: 是“或”从子订单里查的结果也是 orderid 等于 001,002,003 的都出来了
@guwoow: 明白你意思了，你说是只有同时包含a和c的才查出来？
@guwoow: 是或的话 002 也能查出来啦
@会长: 嗯 对的 子查主
0
select a.* from 订单主表
where
orderid in ( select distinct orderid from 订单子表 b where b.name = 'a' or name = 'c')
收获园豆：5
No， 你这种 002 也能查的出来 错误
@llhhll004:
刚才看错题目了
select distinct a.* from 订单主表 a
inner join 订单子表 b on b.name = 'a' and a.orderid = b.orderid inner join 订单子表 c on c.name = 'c' and a.orderid = c.orderid
@wanglgkaka: 见我这种
select * from 主表 where
exists (select * from 子表 as a where a.orderid =主表.orderid and a.name='a')
and exists (select * from 子表 as a where a.orderid =主表.orderid and a.name='c')
这样查出来的就是 001 ， 003的数据
0
select t_p.* from t_p join ( select * from ( select count(*) as rowCount, t.orderid from ( select distinct orderid ,name from t_c where name = 'c' or name = 'a' )t group by t.orderid )tt where tt.rowCount > 1 )ttt on t_p.orderid = ttt.orderid
如果属于同一个主表的字表Name字段值没有重复的，也可以不用distinct
收获园豆：10
不知 你是不是对的
我的
select * from 主表 where
exists (select * from 子表 as a where a.orderid =主表.orderid and a.name='a')
and exists (select * from 子表 as a where a.orderid =主表.orderid and a.name='c')
这样查出来的就是 001 ， 003的数据
@llhhll004: 都行，看看哪个效率高。我对sql也没研究，可以测试一下哪个速度快
1
SELECT t1.OrderId,t3.Name2 FROM #T2 t1 JOIN #T2 t2 ON t2.OrderId = t1.orderId LEFT JOIN #T1 t3 ON t3.OrderId = t1.OrderId WHERE t1.NAME = 'c' AND t2.NAME = 'a'
收获园豆：10
测试语句：
CREATE TABLE #T1 ( OrderId VARCHAR(50), Name2 VARCHAR(50) ) INSERT INTO #T1 SELECT '001', '玩儿' UNION SELECT '002', '玩儿2' UNION SELECT '003', '玩儿3' CREATE TABLE #T2 ( Id INT, OrderId VARCHAR(50), NAME VARCHAR(50) ) INSERT INTO #T2 SELECT 1, '001', 'a' UNION SELECT 2, '002', 'a' UNION SELECT 3, '001', 'c' UNION SELECT 4, '002', 'b' UNION SELECT 5, '002', 'd' UNION SELECT 6, '003', 'a' UNION SELECT 7, '003', 'c' SELECT t1.OrderId, t3.Name2 FROM #T2 t1 JOIN #T2 t2 ON t2.OrderId = t1.orderId LEFT JOIN #T1 t3 ON t3.OrderId = t1.OrderId WHERE t1.NAME = 'c' AND t2.NAME = 'a'
很久没看博问了，看见你写的特意登录说一下：
SELECT t3.OrderId, t3.Name2 FROM #T2 t1 JOIN #T2 t2 ON t2.OrderId = t1.orderId and t1.Name < t2.Name LEFT JOIN #T1 t3 ON t3.OrderId = t1.OrderId WHERE t1.NAME = 'a' AND t2.NAME = 'c'
不过你的答案很好，不过我觉的这样是不会性能好点呢？
@小小刀: 谢谢指教，我看了下执行计划，从这上面看起来性能是差不多的。
我对其中的t1.Name<t2.Name不是很理解呢~
0
问题描述有问题，没猜错的话，目的是查询同时包含name=a和c的订单，1楼是正解
收获园豆：5