今天一個群中的兄弟問一個問題,說有一個表,表中有很多數據,其中有個字段type,希望從表中隨機取出10條記錄,其中有5條type=1另外5條type=0,比如下圖這樣:
我一想,這還不簡單,按照要求,每個取top 5,既然是隨機的取,那麼就order by newid()就是了三,然後把所有數據union起來就得了。所以我立即給出了答案:
select top 5 * from xxx where type=1 order by newid()
union
select top 5 * from xxx where type=0 order by newid()
但是那個兄弟說不對,語法有錯,我乍一看,好像沒有問題把,於是讓他把表定義發給我,我在本機試一下,表定義是:
CREATE TABLE [dbo].[Question](
[id] [int] IDENTITY(1,1) NOT NULL,
[question] [varchar](50) NOT NULL,
[answer] [varchar](50) NOT NULL,
[type] [bit] NOT NULL,
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
於是將我的語句輸進SSMS,結果系統還真是報錯了:Incorrect syntax near the keyword 'ORDER'.
怎麼回事啊?怎麼看也不像有問題的啊。於是我單獨執行
select top 5 * from dbo.Question
where [type]=1
ORDER BY newid()