如何查询 多条记录满足不同值的数据

[jjkmm]

例如 table

rq cb je xm

2012-07-09 00:00:00 晚餐 11.9 艾凤琴
2012-07-09 00:00:00 中餐 10.4 艾凤琴
2012-07-09 00:00:00 早餐 5.2 艾凤琴
2012-07-10 00:00:00 早餐 4.9 艾凤琴
2012-07-10 00:00:00 中餐 10.5 艾凤琴
2012-07-10 00:00:00 晚餐 12 艾凤琴
2011-12-22 00:00:00 晚餐 6.0 艾胡蓉
2011-12-22 00:00:00 中餐 3.2 艾胡蓉
2011-12-22 00:00:00 早餐 4.2 艾胡蓉
2012-06-24 00:00:00 晚餐 7.93 白玲
2012-06-24 00:00:00 中餐 6.89 白玲
2012-06-24 00:00:00 早餐 4.669 白玲
2012-07-04 00:00:00 晚餐 8.23 白珊
2012-07-04 00:00:00 中餐 6.759 白珊
2012-07-04 00:00:00 早餐 4.190 白珊
2011-11-20 00:00:00 晚餐 6.25 班小会
2011-11-20 00:00:00 中餐 7.37 班小会
2011-11-20 00:00:00 早餐 4.280 班小会
2011-12-19 00:00:00 晚餐 7.7 包双双
2011-12-19 00:00:00 中餐 7.5 包双双
2011-12-19 00:00:00 早餐 5.04 包双双
2011-12-22 00:00:00 晚餐 8.6 卜丽玲
2011-12-22 00:00:00 中餐 7.73、 卜丽玲
2011-12-22 00:00:00 早餐 5.629 卜丽玲
2012-04-27 00:00:00 晚餐 5.5 卜美华
2012-04-27 00:00:00 中餐 6.29 卜美华
2012-04-27 00:00:00 早餐 4.2999 卜美华

查询要求
1、同一个人同一天符合早餐≥5.0 及中餐≥10.0 的人员
2、同一个人同一天符合早餐≥5.0 及晚餐≥10.0及中餐≥8.0 的人

望高手指点

[中国风]

select 
a.rq,a.xm
from tableA as a
inner join tableA as b on a.rq=b.rq and a.xm=b.xm and b.cb='中餐' and b.je>=10.00
where a.cb='早餐' and a.je>=5.0


select 
a.rq,a.xm
from tableA as a
inner join tableA as b on a.rq=b.rq and a.xm=b.xm and b.cb='中餐' and b.je>=8.00
inner join tableA as c on a.rq=c.rq and a.xm=c.xm and c.cb='晚餐' and c.je>=10.00
where a.cb='早餐' and a.je>=5.0

[问苍天人生几何]

--1.
select convert(varchar(10),a.rq,120) rq,a.xm from
(select * from @test where cb=N'早餐' and je>=5) a,
(select * from @test where cb=N'中餐' and je>=10) b
where convert(varchar(10),a.rq,120)=convert(varchar(10),b.rq,120) and a.xm=b.xm
--2.
select convert(varchar(10),a.rq,120) rq,a.xm from
(select * from @test where cb=N'早餐' and je>=5) a,
(select * from @test where cb=N'中餐' and je>=8) b,
(select * from @test where cb=N'晚餐' and je>=10) c
where convert(varchar(10),a.rq,120)=convert(varchar(10),b.rq,120) 
	and convert(varchar(10),a.rq,120)=convert(varchar(10),c.rq,120)
	and a.xm=b.xm and a.xm=c.xm

[筱筱澄]

--1
SELECT * FROM [tb] AS t 
WHERE EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='早餐' AND [je]>=5.0 )
AND EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='中餐' AND [je]>=10.0 )
--2
SELECT * FROM [tb] AS t 
WHERE EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='早餐' AND [je]>=5.0 )
AND EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='中餐' AND [je]>=8.0 )
AND EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='晚餐' AND [je]>=10.0 )

[筱筱澄]

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([rq] DATETIME,[cb] VARCHAR(4),[je] NUMERIC(6,4),[xm] VARCHAR(6))
INSERT [tb]
SELECT '2012-07-09 00:00:00','晚餐',11.9,'艾凤琴' UNION ALL
SELECT '2012-07-09 00:00:00','中餐',10.4,'艾凤琴' UNION ALL
SELECT '2012-07-09 00:00:00','早餐',5.2,'艾凤琴' UNION ALL
SELECT '2012-07-10 00:00:00','早餐',4.9,'艾凤琴' UNION ALL
SELECT '2012-07-10 00:00:00','中餐',10.5,'艾凤琴' UNION ALL
SELECT '2012-07-10 00:00:00','晚餐',12,'艾凤琴' UNION ALL
SELECT '2011-12-22 00:00:00','晚餐',6.0,'艾胡蓉' UNION ALL
SELECT '2011-12-22 00:00:00','中餐',3.2,'艾胡蓉' UNION ALL
SELECT '2011-12-22 00:00:00','早餐',4.2,'艾胡蓉' UNION ALL
SELECT '2012-06-24 00:00:00','晚餐',7.93,'白玲' UNION ALL
SELECT '2012-06-24 00:00:00','中餐',6.89,'白玲' UNION ALL
SELECT '2012-06-24 00:00:00','早餐',4.669,'白玲' UNION ALL
SELECT '2012-07-04 00:00:00','晚餐',8.23,'白珊' UNION ALL
SELECT '2012-07-04 00:00:00','中餐',6.759,'白珊' UNION ALL
SELECT '2012-07-04 00:00:00','早餐',4.190,'白珊' UNION ALL
SELECT '2011-11-20 00:00:00','晚餐',6.25,'班小会' UNION ALL
SELECT '2011-11-20 00:00:00','中餐',7.37,'班小会' UNION ALL
SELECT '2011-11-20 00:00:00','早餐',4.280,'班小会' UNION ALL
SELECT '2011-12-19 00:00:00','晚餐',7.7,'包双双' UNION ALL
SELECT '2011-12-19 00:00:00','中餐',7.5,'包双双' UNION ALL
SELECT '2011-12-19 00:00:00','早餐',5.04,'包双双' UNION ALL
SELECT '2011-12-22 00:00:00','晚餐',8.6,'卜丽玲' UNION ALL
SELECT '2011-12-22 00:00:00','中餐',7.73,'卜丽玲' UNION ALL
SELECT '2011-12-22 00:00:00','早餐',5.629,'卜丽玲' UNION ALL
SELECT '2012-04-27 00:00:00','晚餐',5.5,'卜美华' UNION ALL
SELECT '2012-04-27 00:00:00','中餐',6.29,'卜美华' UNION ALL
SELECT '2012-04-27 00:00:00','早餐',4.2999,'卜美华'
--------------开始查询--------------------------

--1
SELECT * FROM [tb] AS t 
WHERE EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='早餐' AND [je]>=5.0 )
AND EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='中餐' AND [je]>=10.0 )
----------------结果----------------------------
/* 
rq                      cb   je                                      xm
----------------------- ---- --------------------------------------- ------
2012-07-09 00:00:00.000 晚餐   11.9000                                 艾凤琴
2012-07-09 00:00:00.000 中餐   10.4000                                 艾凤琴
2012-07-09 00:00:00.000 早餐   5.2000                                  艾凤琴

(3 行受影响)


*/

--2
SELECT * FROM [tb] AS t 
WHERE EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='早餐' AND [je]>=5.0 )
AND EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='中餐' AND [je]>=8.0 )
AND EXISTS
(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='晚餐' AND [je]>=10.0 )


----------------结果----------------------------
/* 
rq                      cb   je                                      xm
----------------------- ---- --------------------------------------- ------
2012-07-09 00:00:00.000 晚餐   11.9000                                 艾凤琴
2012-07-09 00:00:00.000 中餐   10.4000                                 艾凤琴
2012-07-09 00:00:00.000 早餐   5.2000                                  艾凤琴

(3 行受影响)


*/

[jjkmm]

我想查出对应的明细记录，不只只是日期和姓名，比如：
2012-07-09 00:00:00 晚餐 11.9 艾凤琴
2012-07-09 00:00:00 中餐 10.4 艾凤琴
2012-07-09 00:00:00 早餐 5.2 艾凤琴
2011-12-22 00:00:00 晚餐 8.6 卜丽玲
2011-12-22 00:00:00 中餐 7.73、 卜丽玲
2011-12-22 00:00:00 早餐 5.629 卜丽玲

这样子

[Quote=引用楼主 的回复:]
例如 table

rq cb je xm

2012-07-09 00:00:00 晚餐 11.9 艾凤琴
2012-07-09 00:00:00 中餐 10.4 艾凤琴
2012-07-09 00:00:00 早餐 5.2 艾凤琴
2012-07-10 00:00:00 早餐 4.9 艾凤琴
2012-0……
[/Quote]