Diaplay唱片记录 [英] Diaplay record rendomly

问题描述

亲爱的先生

我在SQL Server中有一张下表.我想以这样一种方式编写查询:每次我运行查询时，它都会随机显示每个类型"中的一条记录.我的意思是，当我运行查询时，它应该显示水果"类型的一条记录和冷饮"类型的一条记录.但是每次它应该随机显示记录.

产品
--------

id类型名称
--- ----- ------
1个水果橙
2冷饮可口可乐
3冷饮美琳达
4个水果香蕉
5个水果苹果
6种水果葡萄


先生，请帮我.


Sashibhusan Meher

Dear Sir

I have a following table in SQL Server. I want to write a query in such a way that each time i run the query it will display one record from each ''Type'' randomly. I mean when i run the query it should display one record from ''fruit'' type and one record from ''cold drinks'' type. But every time it should display records randomly.

product
--------

id type name
--- ----- ------
1 fruits orange
2 cold drinks Coca-cola
3 cold drinks mirinda
4 fruits banana
5 fruits apple
6 fruits grapes


kindly help me Sir.


Sashibhusan Meher

推荐答案

分别制作两列水果和冷饮，并在查询后执行查询以获取随机值.

Make two columns of fruit and colddrinks separately and fire following Query to get random values.

SELECT TOP 1 fruit, colddrinks FROM product ORDER BY NEWID()

这里是:

Here it is :

IF OBJECT_ID(N'tempdb..#pr') IS NOT NULL
  drop table #pr
  
select  ID, [type] , name , abs(CHECKSUM(NewId()))   rnd   into #pr from   product

;with a(t , mr) as
(
select type, MAX(rnd) rnd from #pr group by type
)
select p.id, p.type, p.name from a inner join #pr p on a.t = p.type and a.mr = p.rnd

避免使用临时表是不可能的，因为在联接中重新生成了随机字段并破坏了我的所有计划，所以我选择了临时表.

祝你好运

It was impossible to avoid temporary tables because the random field was regenerated in joins and ruined all of my plans so I went for a temporary table.

Good Luck

如果您想显示随机记录，请尝试一下！

If you''d like to display a random record, try this up!

SELECT TOP 1 * FROM product
ORDER BY NEWID()

那应该做.

问候，
爱德华

That should do it.

Regards,
Eduard

这篇关于Diaplay唱片记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！