高难度的SQL求教！

Question

CREATE TABLE TB([供应商] VARCHAR(4), [货品] VARCHAR(4), [单位] NVARCHAR(1), [数量] INT, [状态] NVARCHAR(2))
INSERT TB 
SELECT 'G001', 'A001', N'个', 100, N'镀A' UNION ALL 
SELECT 'G001', 'A001', N'个', 101, N'镀B' UNION ALL 
SELECT 'G001', 'A001', N'个', 102, N'镀C' UNION ALL 
SELECT 'G001', 'A001', N'个', 103, N'镀D' UNION ALL 
SELECT 'G001', 'A001', N'个', 104, N'镀E' UNION ALL 
SELECT 'G001', 'A001', N'个', 105, N'镀F' UNION ALL 
SELECT 'G001', 'A001', N'个', 106, N'镀G' UNION ALL 
SELECT 'G001', 'A001', N'个', 107, N'镀H' UNION ALL 
SELECT 'G001', 'A001', N'个', 108, N'镀I' UNION ALL 
SELECT 'G001', 'A001', N'个', 109, N'镀J' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀A' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀B' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀C' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀D' UNION ALL 
SELECT 'G001', 'B001', N'个', 750, N'镀D' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀E' UNION ALL 
SELECT 'K001', 'A001', N'个', 300, N'镀E' UNION ALL 
SELECT 'K001', 'A001', N'个', 400, N'镀E'DECLARE @STR NVARCHAR(4000)
DECLARE @ID INT
SET @STR=N''
SET @ID=1SELECT @STR=@STR+N','+N'SUM(CASE WHEN [状态]=N'''+[状态]+N''' THEN [数量] ELSE 0 END) AS STATUS'+RTRIM(@ID),
       @ID=@ID+1
FROM (SELECT DISTINCT [状态] FROM TB ) TSET @STR=N'SELECT [供应商],[货品],[单位]'+@STR+N',TOTAL=(SELECT SUM([数量]) FROM TB WHERE [供应商]=T.[供应商] AND [货品]=T.[货品] AND [单位]=T.[单位]) FROM TB T GROUP BY [供应商],[货品],[单位]'
--PRINT @STREXEC(@STR)
DROP TABLE TB
/*
供应商  货品   单位   STATUS1     STATUS2     STATUS3     STATUS4     STATUS5     STATUS6     STATUS7     STATUS8     STATUS9     STATUS10    TOTAL
---- ---- ---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
G001 A001 个    100         101         102         103         104         105         106         107         108         109         1045
G001 B001 个    200         200         200         950         200         0           0           0           0           0           1750
K001 A001 个    0           0           0           0           700         0           0           0           0           0           700*/

Answer 1

CREATE TABLE TB([供应商] VARCHAR(4), [货品] VARCHAR(4), [单位] NVARCHAR(1), [数量] INT, [状态] NVARCHAR(2))
INSERT TB 
SELECT 'G001', 'A001', N'个', 100, N'镀A' UNION ALL 
SELECT 'G001', 'A001', N'个', 101, N'镀B' UNION ALL 
SELECT 'G001', 'A001', N'个', 102, N'镀C' UNION ALL 
SELECT 'G001', 'A001', N'个', 103, N'镀D' UNION ALL 
SELECT 'G001', 'A001', N'个', 104, N'镀E' UNION ALL 
SELECT 'G001', 'A001', N'个', 105, N'镀F' UNION ALL 
SELECT 'G001', 'A001', N'个', 106, N'镀G' UNION ALL 
SELECT 'G001', 'A001', N'个', 107, N'镀H' UNION ALL 
SELECT 'G001', 'A001', N'个', 108, N'镀I' UNION ALL 
SELECT 'G001', 'A001', N'个', 109, N'镀J' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀A' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀B' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀C' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀D' UNION ALL 
SELECT 'G001', 'B001', N'个', 750, N'镀D' UNION ALL 
SELECT 'G001', 'B001', N'个', 200, N'镀E' UNION ALL 
SELECT 'K001', 'A001', N'个', 300, N'镀E' UNION ALL 
SELECT 'K001', 'A001', N'个', 400, N'镀E'
goCREATE PROC P_getData
ASDECLARE @STR NVARCHAR(4000)
DECLARE @ID INT
SET @STR=N''
SET @ID=1SELECT @STR=@STR+N','+N'SUM(CASE WHEN [状态]=N'''+[状态]+N''' THEN [数量] ELSE 0 END) AS STATUS'+RTRIM(@ID),
       @ID=@ID+1
FROM (SELECT DISTINCT [状态] FROM TB ) TSET @STR=N'SELECT [供应商],[货品],[单位]'+@STR+N',TOTAL=(SELECT SUM([数量]) FROM TB WHERE [供应商]=T.[供应商] AND [货品]=T.[货品] AND [单位]=T.[单位]) FROM TB T GROUP BY [供应商],[货品],[单位]'
--PRINT @STREXEC(@STR)
GOEXEC P_getDataDROP TABLE TB
DROP PROC P_getData
/*
供应商  货品   单位   STATUS1     STATUS2     STATUS3     STATUS4     STATUS5     STATUS6     STATUS7     STATUS8     STATUS9     STATUS10    TOTAL
---- ---- ---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
G001 A001 个    100         101         102         103         104         105         106         107         108         109         1045
G001 B001 个    200         200         200         950         200         0           0           0           0           0           1750
K001 A001 个    0           0           0           0           700         0           0           0           0           0           700(3 row(s) affected)
*/

Answer 2

看这个就明白了
PRINT @STR

Answer 3

谢谢，但是在函数里面不能调用存储过程呀，因为我是想做成函数。 我到时候查询时就调用
SELECT * FROM  GOODSDATA(@FROMDATE , @TODATE )

Answer 4

[code=SQL]--你可以在存储过程加两个参数
CREATE PROC P_getData
@FROMDATE datetime, 
@TODATE datetime
AS
...--然后这样调用:
EXEC P_getData @FROMDATE, @TODATE
[/code]

Answer 5

谢谢，
请问能不能设置成函数
能不能用 SELECT 去查询呀？ 谢谢！！

Answer 6

5楼穷举肯定不能满足大批量数据
case ** when ** then ** end  **,

Answer 7

有没有其它兄弟帮忙下,希望能做成个函数   我用  SELECT 语句去查询 ,谢谢！！

Answer 8

自定义函数不动态EXEC，可以写成静态，如果要动态的，还是用储存过程吧！

Answer 9

写个静态的例子：
-->创建自定义函数
CREATE FUNCTION F_GETDATA()
RETURNS TABLE
AS
RETURN
SELECT [供应商],[货品],[单位],
  SUM(CASE WHEN [状态]=N'镀A' THEN [数量] ELSE 0 END) AS STATUS1,
  SUM(CASE WHEN [状态]=N'镀B' THEN [数量] ELSE 0 END) AS STATUS2,
  SUM(CASE WHEN [状态]=N'镀C' THEN [数量] ELSE 0 END) AS STATUS3,
  SUM(CASE WHEN [状态]=N'镀D' THEN [数量] ELSE 0 END) AS STATUS4,
  SUM(CASE WHEN [状态]=N'镀E' THEN [数量] ELSE 0 END) AS STATUS5,
  SUM(CASE WHEN [状态]=N'镀F' THEN [数量] ELSE 0 END) AS STATUS6,
  SUM(CASE WHEN [状态]=N'镀G' THEN [数量] ELSE 0 END) AS STATUS7,
  SUM(CASE WHEN [状态]=N'镀H' THEN [数量] ELSE 0 END) AS STATUS8,
  SUM(CASE WHEN [状态]=N'镀I' THEN [数量] ELSE 0 END) AS STATUS9,
  SUM(CASE WHEN [状态]=N'镀J' THEN [数量] ELSE 0 END) AS STATUS10,
  TOTAL=(SELECT SUM([数量]) 
FROM TB 
WHERE [供应商]=T.[供应商] AND [货品]=T.[货品] AND [单位]=T.[单位]) 
FROM TB T 
GROUP BY [供应商],[货品],[单位]
go
-->调用：
select * from f_getdata()
/*
供应商  货品   单位   STATUS1     STATUS2     STATUS3     STATUS4     STATUS5     STATUS6     STATUS7     STATUS8     STATUS9     STATUS10    TOTAL
---- ---- ---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
G001 A001 个    100         101         102         103         104         105         106         107         108         109         1045
G001 B001 个    200         200         200         950         200         0           0           0           0           0           1750
K001 A001 个    0           0           0           0           700         0           0           0           0           0           700(3 行受影响)
*/