sql查询无法正常工作 [英] sql query not working correctly

问题描述

USE [cms]
GO
/****** Object:  StoredProcedure [dbo].[SpCateProductsInfo]    Script Date: 10/14/2012 00:50:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SpCateProductsInfo](@cate_id int)
as
begin
select sb.subcate_name, sum(p.qty) as totalqty, 
(select top 1 pimg_mid1 from product join subcategory
on product.subcate_id=
(
	select top 1 subcate_id
	from subcategory
	where cate_id=@cate_id
	order by NEWID()
)
order by NEWID()) as pimg_mid1
from subcategory sb 
  join product p on p.subcate_id = sb.subcate_id
where sb.cate_id = @cate_id
group by sb.subcate_name


end

它正在运行f9，但问题是 pimg_mid1 列包含相同的条目(与pimg_mid1列有关的每一行都包含相同的数据)

谁能告诉我这个错误和解决方案?

it is working f9, But the problem is that the pimg_mid1 column contains same entries (every row regarding the pimg_mid1 column contains same data)

can any 1 tell me abt this error and the solution ???

推荐答案

您的查询在逻辑上是错误的:

如果您全部需要pimg_mid1

Your query is logically wrong:

If you need all pimg_mid1

select sb.subcate_name, sum(p.qty) as totalqty,
p.pimg_mid1
from subcategory sb
join product p on p.subcate_id = sb.subcate_id
where sb.cate_id = @cate_id
group by sb.subcate_name, p.pimg_mid1

如果您需要一个(最新)，请使用Max

If you need one(latest) - use Max

select sb.subcate_name, sum(p.qty) as totalqty,
MAX(p.pimg_mid1) AS pimg_mid1
from subcategory sb
join product p on p.subcate_id = sb.subcate_id
where sb.cate_id = @cate_id
group by sb.subcate_name

这篇关于sql查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！