在SQL Server中优化SELECT查询
问题描述:
我想从SQL Server表中区分两列。我得到这个结果 - 我可以优化这个查询吗?在SQL Server中优化SELECT查询
create TABLE #Temporary_tbl
(
ProductColour VARCHAR(50),
ProductSize VARCHAR(20),
)
insert into #Temporary_tbl (ProductColour)
select distinct productcolour
from shoptransfer
insert into #Temporary_tbl (ProductSize)
select distinct ProductSize
from shoptransfer
select * from #Temporary_tbl
我试试这个:
select distinct ProductColour, null as ProductSize
from shoptransfer
union all
select distinct null as ProductColor, ProductSize
from shoptransfer
答
我认为这个问题是您的期望:我猜要颜色中的一种独特的名单和大小的一个独特的名单,但你希望它们出现在相同的结果表,就像这样:
Colour Size
blue 10
green 12
orange 14
即使有蓝色&大小10等之间没有关系这不是数据有很大的表现,因为会发生什么WH有5种颜色,但有100种尺寸。
您可以通过分别选择每个DISTINCT列表和ROW_NUMBER(),然后连接行号来完成此操作。这不是SQL的真正用处,但我可以理解,有时候您想要做这种事情来报告原因。
如
select productSize, productColour
from (
-- get the colours and a row number
select productColour, row_number() over (order by productColour) as rownum
from (select distinct productColour from shoptransfer) as c1
) as colours
-- full outer join so the row numbers match and you get all the rows from both
full outer join (
-- get the sizes and a row number
select productSize, row_number() over (order by productSize) as rownum
from (select distinct productSize from shoptransfer) as s1
) as sizes
on colours.rownum = sizes.rownum
答
;WITH CTE AS
(
select ProductColour , null as ProductSize
from shoptransfer
GROUP BY ProductColour
union all
select null as ProductColor, ProductSize
from shoptransfer
GROUP BY ProductSize
)
SELECT * FROM CTE
答
取决于你如何想在座数据的其他建议也许+ PIVOT
select * from (
SELECT distinct
[ProductColor] as 'tata_column', 'ProductColor' as 'P_TAG'
FROM shoptransfer
union all
SELECT distinct
[ProductSize] as 'data_colunm' , 'ProductSize' as 'P_TAG'
FROM shoptransfer
) A
我将消除TEMP表运行更快
请注意与大记录集使用“WITH CLAUSE”将放慢,并可能崩溃服务器!
你的查询是好的。什么是问题? – 2014-09-29 16:47:13