如何在sql中使用decending order [英] how to use decending order use in sql

问题描述

我在这个查询中使用..



如何使用前1个下降顺序



请帮我任何身体

i have using in this query..

how to use top 1 decending order in

pls help me any body

select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=1 union
select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id,   b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=2   union
select  distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id,  b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=3  union

select distinct  top 1  a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=4 union
select distinct  top 1 a.product_price, a.product_id,  a.product_name,b.category_id,  b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=5 union
select  distinct  top 1 a.product_price, a.product_id, a.product_name, b.category_id, b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=6

推荐答案

首先摆脱所有这些联盟 - 用IN子句 [ ^ ]

下一步 - DISTINCT需要在TOP 1之后出现

最后 ORDER BY DESC [ ^ ]结束。

如果你还在使用UNION，那么订单仍然会在最后。您的查询变为

First of all get rid of all those unions - replace them with an IN clause[^]
Next - the DISTINCT needs to come after the TOP 1
Finally ORDER BY DESC[^] goes at the end.
If you were still using UNION the order by would still go right at the end. Your query becomes

select top 1 distinct  a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image
from tbl_products_web as a
inner join tbl_category_web  as b on a.category_id=b.category_id
where b.category_id IN (1,2,3,4,5,6)
ORDER BY [whichever column here]

有几种方法可以实现这个目标



选项1 - 使用公用表格式

There are a few ways you can achieve this

Option 1 - using Common Table Expressions

-- NOTE ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
-- so the select from C1, C2 etc redefines the list of columns
with C1 as (select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated 
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=1 order by a.image_updated desc),
C2 as (select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id,   b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=2 order by a.image_updated desc),
C3 as (select  distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id,  b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=3 order by a.image_updated desc),
C4 as (select distinct  top 1  a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=4 order by a.image_updated desc),
C5 as (select distinct  top 1 a.product_price, a.product_id,  a.product_name,b.category_id,  b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=5 order by a.image_updated desc),
C6 as (select  distinct  top 1 a.product_price, a.product_id, a.product_name, b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=6 order by a.image_updated desc)
select product_price, product_id, product_name,category_id, category_name,small_image,product_image from C1
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C2
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C3
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C4
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C5
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C6

选项2 - 使用临时表和循环

Option 2 - using a temporary table and a loop

create table #temp
(
	product_price float,
	product_id int,
	product_name varchar(255),
	category_id int,
	category_name varchar(255),
	small_image varchar(max),
	product_image varchar(max),
	image_updated datetime
)

declare @i int = 1
while @i <= 6
begin
	insert into #temp select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated 
		from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=@i order by a.image_updated desc
	set @i = @i + 1
end
select product_price, product_id, product_name,category_id, category_name,small_image,product_image 
from #temp

如果您使用的是SQL Server 2005或更新版本，您可以使用分析函数RANK（）来实现您想要的效果。

最好的部分是它是通用的，你不需要事先知道类别

If you are using SQL Server 2005 or newer you can use the analytic function RANK() to achieve what you want.
The best part is that it's generic and you don't need to know the categories in beforehand

WITH CTE AS (
    SELECT  a.product_price
           ,a.product_id
           ,a.product_name
           ,b.category_id
           ,b.category_name
           ,a.small_image
           ,a.product_image
           ,RANK() OVER (PARTITION BY b.category_id ORDER BY a.image_updated DESC) AS Rank
    FROM    tbl_products_web as a 
    INNER JOIN tbl_category_web as b ON a.category_id = b.category_id
    )
SELECT  a.product_price
       ,a.product_id
       ,a.product_name
       ,b.category_id
       ,b.category_name
       ,a.small_image
       ,a.product_image
FROM    CTE
WHERE   Rank = 1

请注意，如果你有两个图像与最后一个image_updated相同的类别，你将得到这个查询。

如果这是一个问题，你可以交换RANK（）为ROW_NUMBER（）。

但你不知道哪一行被选中。

Note that if you have two images with the same last image_updated for a category you'll get both with this query.
If this is a problem you can exchange the RANK() for a ROW_NUMBER().
But you won't know which of the row that get selected.

SELECT DISTINCT TOP 1...
FROM ...

ORDER BY MyColumn DESC

这篇关于如何在sql中使用decending order的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！