两个表
Type 表
TypeID TypeName
0001 水果
0002 动物Result 表
ResultID TypeID ResultName ResultOrder
0001 0001 苹果 1
0002 0001 梨子 2
0003 0002 狐狸 2
0004 0002 大象 1
0005 0002 狮子 3想得到这样一个结果,就是按照类型统计出每个类型包含的结果数量,同时能够显示出该类型排序第一的结果
想得到的结果如下TypeID TypeName TypeCount ResultName
0001 水果 2 苹果
0002 动物 3 大象
Type 表
TypeID TypeName
0001 水果
0002 动物Result 表
ResultID TypeID ResultName ResultOrder
0001 0001 苹果 1
0002 0001 梨子 2
0003 0002 狐狸 2
0004 0002 大象 1
0005 0002 狮子 3想得到这样一个结果,就是按照类型统计出每个类型包含的结果数量,同时能够显示出该类型排序第一的结果
想得到的结果如下TypeID TypeName TypeCount ResultName
0001 水果 2 苹果
0002 动物 3 大象
t1.TypeID ,t1.TypeName,TypeCount=count(*),t3.ResultName
from
t1
join
t2 on t1.TypeID=t2.TypeID
join
t2 t3 on t3.TypeID=t2.TypeID and t3.ResultOrder=1
group by t1.TypeID ,t1.TypeName ,t3.ResultName
--> 生成测试数据: #Type
IF OBJECT_ID('tempdb.dbo.#Type') IS NOT NULL DROP TABLE #Type
CREATE TABLE #Type (TypeID VARCHAR(4),TypeName NVARCHAR(4))
INSERT INTO #Type
SELECT '0001','水果' UNION ALL
SELECT '0002','动物'
--> liangCK小梁 于2008-10-09
--> 生成测试数据: #Result
IF OBJECT_ID('tempdb.dbo.#Result') IS NOT NULL DROP TABLE #Result
CREATE TABLE #Result (ResultID VARCHAR(4),TypeID VARCHAR(4),ResultName NVARCHAR(4),ResultOrder INT)
INSERT INTO #Result
SELECT '0001','0001','苹果','1' UNION ALL
SELECT '0002','0001','梨子','2' UNION ALL
SELECT '0003','0002','狐狸','2' UNION ALL
SELECT '0004','0002','大象','1' UNION ALL
SELECT '0005','0002','狮子','3'--SQL查询如下:SELECT t.TypeID,TypeName,m.TypeCount,ResultName
FROM #Type AS t
LEFT OUTER JOIN
(
SELECT *
FROM #Result AS r
WHERE ResultOrder=
(
SELECT MIN(ResultOrder)
FROM #Result
WHERE r.TypeID=TypeID
)
) AS r
ON t.TypeID=r.TypeID
LEFT OUTER JOIN
(
SELECT TypeID,COUNT(*) AS TypeCount
FROM #Result
GROUP BY TypeID
) AS m
ON t.TypeID=m.TypeID /*
TypeID TypeName TypeCount ResultName
------ -------- ----------- ----------
0001 水果 2 苹果
0002 动物 3 大象(2 行受影响)*/
A.*,
B.TypeCount, B.ResultName
FROM [Type] A, Result B,
(
SELECT
TypeID,
ResultOrder = MIN(ResultOrder)
FROM Result
GROUP BY TypeID
)B1
WHERE A.TypeID = B.TypeID
AND B.ResultOrder = B1.ResultOrder
AND B.TypeID = B1.TypeID
from Type a
join Result b on a.TypeID=b.TypeID
join Result c on c.TypeID=b.TypeID and c.ResultOrder=1
group by a.TypeID ,a.TypeName ,c.ResultName
SELECT
A.*,
B.TypeCount, B.ResultName
FROM [Type] A
CROSS APPLY(
SELECT TOP 1
TypeCount, ResultName
FROM Result
WHERE A.TypeID = TypeID
ORDER BY ResultOrder
)B
from #Type a
join #Result b on a.TypeID=b.TypeID
join #result c on c.TypeID=b.TypeID and c.ResultOrder=1
group by a.TypeID ,a.TypeName ,c.ResultName
INSERT @a SELECT '0001' ,'水果'
union all select '0002' ,'动物' DECLARE @b table(ResultID varchar(20), TypeID varchar(20), ResultName varchar(20), ResultOrder INt)
INSERT @b SELECT '0001' ,'0001' ,'苹果', 1
UNION ALL SELECT '0002' ,'0001' ,'梨子', 2
UNION ALL SELECT '0003' ,'0002' ,'狐狸', 2
UNION ALL SELECT '0004' ,'0002' ,'大象', 1
UNION ALL SELECT '0005' ,'0002' ,'狮子', 3 select a.TypeID,
a.TypeName,
count(1) TypeCount,
ResultName =(select ResultName from @b where TypeID=a.TypeID and ResultOrder=min(b.ResultOrder))
from @a a inner join @b b
on a.TypeId=b.TypeId
group by a.TypeId,a.TypeName--result
/*TypeID TypeName TypeCount ResultName
------ -------------------- ----------- --------------------
0002 动物 3 大象
0001 水果 2 苹果(所影响的行数为 2 行)*/
(select top 1 ResultName from tb2 where TypeID=tb1.TypeID )from tb1