有意思的SQL题目

wuxiang_123

今天在回答一个会员的问题，问题如下：
需要将下图A列数值区分正负值，并列出返回如C:D列效果。


使用VBA、和函数相信对于各位VBA和函数高手都很简单（偶这两方面都是菜鸟，不会），如果结果不需要返回重复值，那么，使用SQL也很简单，可以使用我的转置贴语句实现：

1. 
   
2. TRANSFORM MIN(数值)
   
3. SELECT NULL FROM
   
4. (SELECT A.正负,A.数值,SUM(1) AS 排名 FROM
   
5. (SELECT DISTINCT SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET1$] WHERE 数值)A,
   
6. (SELECT DISTINCT SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET1$] WHERE 数值)B
   
7. WHERE A.正负=B.正负 AND A.数值>=B.数值
   
8. GROUP BY A.正负,A.数值)
   
9. GROUP BY 排名
   
10. PIVOT 正负

复制代码

但结果要求返回重复值，而SQL却没有行的概念，无法区分相同项，此时，我们需要使用辅助列来完成：



辅助的作用在于为我们区分相同的项，也可以为我们获取每个记录在数据源中的位置，此时，我们就可以利用辅助列使用以下语句：

1. TRANSFORM MIN(数值)
   
2. SELECT NULL FROM
   
3. (SELECT A.正负,A.数值,A.辅助,SUM(1) AS 位置 FROM
   
4. (SELECT 辅助,SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET1$] WHERE 数值)A,(SELECT 辅助,SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET1$] WHERE 数值)B
   
5. WHERE A.正负=B.正负 AND A.辅助<=B.辅助
   
6. GROUP BY A.正负,A.数值,A.辅助)
   
7. GROUP BY 位置
   
8. PIVOT 正负

复制代码

也许你会疑问：那新增数据不就是需要重新在辅助列输入公式？此时，我们完全可以借用列表功能完成公式的自动填充，然后我们隐藏数据源中的辅助列就可以了。


那有没有不在数据源插入辅助列的方法呢？这时候我们需要使用到【连接属性】－【属性】中的【包含行号】。

此时将出现一个“_RowNum” 新字段，作用等价于我们上文使用的辅助列，在返回的结果表中插入了一个行号，然后我们再以返回结果作为数据源进行交叉表查询。

1. TRANSFORM MIN(数值)
   
2. SELECT NULL FROM
   
3. (SELECT A.正负,A.数值,A.[_RowNum],SUM(1) AS 位置 FROM
   
4. (SELECT [_RowNum],SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET4$] WHERE 数值)A,(SELECT [_RowNum],SWITCH(数值>0,"正数",数值<0,"负数") AS 正负,数值 FROM [SHEET4$] WHERE 数值)B
   
5. WHERE A.正负=B.正负 AND A.[_RowNum]<=B.[_RowNum]
   
6. GROUP BY A.正负,A.数值,A.[_RowNum])
   
7. GROUP BY 位置
   
8. PIVOT 正负

复制代码

使用此方法，纯属取巧，不能适应数据源的动态更新，不可取。




该贴已经同步到 wuxiang_123的微博

tsoyzm

吴版，你研究太深入了

moon_2000

这里文章太大了。SQL那几句基本语句我也学了用了一段时间了，但如此游刃有余，尚是学习目标啊。

张雄友

moon_2000 发表于 2012-11-17 13:09
这里文章太大了。SQL那几句基本语句我也学了用了一段时间了，但如此游刃有余，尚是学习目标啊。

是很庞大的。

Ｍａs_T_ｅr

香帅厉害~  鼓捣了半天还是绕到转置这里了，级别太高，不懂~

amazeyeli

这个问题在Server SQL中比较好解决
[code=sql]--方法一
DECLARE @TB1 TABLE(ID INT IDENTITY,NUM INT)
DECLARE @TB2 TABLE(ID INT IDENTITY,NUM INT)
INSERT INTO @TB1
SELECT NUM FROM TEST WHERE num > 0;
INSERT INTO @TB2
SELECT NUM FROM TEST WHERE num < 0;
SELECT A.NUM,B.NUM
FROM @TB1 AS A
LEFT JOIN @TB2 AS B
ON A.ID = B.ID;

--方法二
;WITH A AS
(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS ID,NUM
FROM TEST WHERE num > 0),
B AS
(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS ID,NUM
FROM TEST WHERE num < 0)
SELECT A.NUM,B.NUM
FROM A
LEFT JOIN B
ON A.ID = B.ID;[/code]

201228

能做到版主一个级别的，都是神一样的人物！！！！