SQL Server在“选择查询”中添加额外的列

问题描述

嗨亲爱的，





任何人都可以帮助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.. :)

推荐答案

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在“选择查询”中添加额外的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！