SQL Server在“选择查询”中添加额外的列 [英] SQL Server Add extra columns in Select Query
问题描述
嗨亲爱的，
任何人都可以帮助meeeee ............
我在SQLSERVER中有一个表如下
Hi Dear All,
Could any one please help meeeee............
I have a table in SQLSERVER as follows
id PName MinAmount
1 aa 2.3
2 aa 3.5
3 bbb 6.5
4 bbb 5.2
5 bbb 4.2
6 bbb 7.2
7 bbb 5.6
8 bbb 4.3
9 bbb 8.7
10 cc 2.8
11 cc 3.1
12 cc 2.9
13 dd 5.1
14 ee 9.2
15 ee 8.5
16 ee 7.3
我想要从这个表中得到一个结果，添加2个额外的列来显示
最便宜的PNames。第1列中的chepest 2 PNames和接下来2列中最便宜的2，如下所示。
I would like to get a result from this table as add 2 extra columns for showing
cheapest PNames . chepest 2 PNames in column 1 and next 2 cheapest in next 2 column as shown below.
id PName VeryCheap Moderate
1 aa 2.3 Null
3.5 Null
2 bbb 4.2 5.2
4.3 5.6
3 cc 2.8 3.1
2.9 Null
4 dd 5.1 Null
5 ee 7.3 9.2
8.5 Null
请告诉我如何写这个选择查询？
谢谢你的朋友...很开心... tc .. :)
Kindly tell me how to write the select query for this ?
Thank You Friends... hav a nice time...tc.. :)
推荐答案
我好找到了一种方法，可以使用PIVOT和PARTITION以及临时表来获得大部分路径，但它并不优雅，并且完全不符合您的预期输出...
首先，预期结果中的id似乎没有意义，因为您希望将多个ID组合成一组结果。我忽略了它。
其次，我不知道你有什么方式呈现结果（下一行的部分数据）。 />
所以这里......
首先，我按照PName对所有数据进行排名并仅滤除排名1到4（我在排名前加了'A'，因为我想稍后使用该列作为列名）
Well I found a way to get most of the way there using PIVOT and PARTITION and a temporary table but it's not elegant and it doesn't exactly match your expected outputs ...
Firstly, the id in your expected results seems meaningless as you want to combine multiple ids into a single set of results. I've ignored it.
Secondly, I don't know of any way of presenting the results as you have (partial data on the next row).
So here goes...
Firstly I ranked all of the data by PName and filtered out only rankings 1 through 4 (I've put an 'A' in front of the rank because I want to use that column as a column name later)
select
PName,
'A' + cast(rn as varchar) as colname,
MinAmount
from
(select PName, MinAmount,
row_number() over (partition by PName order by MinAmount) as rn
from test) rankings
where rn IN (1,2,3,4)这给了我以下结果...
This gave me the following results...
PNAME COLNAME MINAMOUNT
aa A1 2.3
aa A2 3.5
bbb A1 4.2
bbb A2 4.3
bbb A3 5.2
bbb A4 5.6
cc A1 2.8
cc A2 2.9
cc A3 3.1
dd A1 5.1
ee A1 7.3
ee A2 8.5
ee A3 9.2 然后我使用PIVOT对该数据进行排名A1到A4作为列，将结果放入临时表中。整个查询变为
I then used PIVOT on that data to get the rankings A1 to A4 as columns, putting the results into a temporary table. The whole query becomes
with filtered as
(
select
PName,
'A' + cast(rn as varchar) as colname,
MinAmount
from
(select PName, MinAmount,
row_number() over (partition by PName order by MinAmount) as rn
from test) rankings
where rn IN (1,2,3,4)
)
select *
into #test1
FROM (
SELECT
PName,
colname,
MinAmount
FROM filtered
) as s
PIVOT
(
SUM(MinAmount)
FOR [colname] IN (A1,A2,A3,A4)
) as apivot这给出了以下结果 - 根据您打算如何显示结果，这可能实际上是您所需要的（在这种情况下不需要临时表）
This gave the following results - which might actually be all you need depending on how you intend to display the results (in which case no need for the temporary table)
PNAME A1 A2 A3 A4
aa 2.3 3.5 (null) (null)
bbb 4.2 4.3 5.2 5.6
cc 2.8 2.9 3.1 (null)
dd 5.1 (null) (null) (null)
ee 7.3 8.5 9.2 (null)
I f使用以下查询消失，以使结果非常接近您发布的预期结果
I finished off with the following query to get the results fairly close to the expected results you posted
SELECT PNAME, A1 as VeryCheap, A3 as Moderate FROM #test1
UNION ALL
SELECT PNAME, A2, A4 FROM #test1 where A2 is not null
ORDER BY 1,2注意在第二个查询上检查 not null 以过滤掉一些垃圾
Note the check for not null on the second query to filter out some of the "rubbish"
这篇关于SQL Server在“选择查询”中添加额外的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
