Select from another select without temporary table?

debugcn 에 게시 Dev

Mateusz

There is table of numbers containing values [1,1,2,3,7,3,4,2,7] I want to select only duplicates so output set would contain [1,2,3,7] so 4 is filtered out.

I have code that I did like this:

DROP TABLE #tempTable;
SELECT  [numbers] as nums, COUNT(*) as cny
INTO #tempTable
  FROM [testBase].[dbo].[numbers] group by numbers;

SELECT nums from #tempTable where nums > 1;

Now I would like to know if I can get it without first selecting to #tempTable? Something more like select from select or is it only way I can do it?

hgulyan

The same in one query.

Conditions that are made after GROUP BY should be added to HAVING clause.

SELECT  [numbers] as nums, COUNT(*) as cny
FROM [testBase].[dbo].[numbers] 
GROUP BY numbers
HAVING COUNT(*) > 1

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사

Select from another select without temporary table?

Select from another select without temporary table?

select * from (select * from table)

Select from one table and order by column of another table

best way to select data from one table or another

MySql select by excluding all id from another table

SELECT FROM table, ORDER BY IF (...)

PostgreSQL return select results AND add them to temporary table?

Should I use a nested select or create a temporary table for this situation in SQL?

Select all fields from a table where a field in another table with an ID is equal to a string

SELECT array of cols FROM table

RegEx in select from table in db

SELECT * FROM table Where CURDATE ()

select a field values that are not found in another table

MySQL select data from table and inner join the union all selection from another two identical tables

CREATE TABLE AS select * from partitioned table

SELECT * INTO [newdatabase]. [table] FROM [otherdatabase]. [table]

Select results from one query to another

How to select column in table by creating row in another table in MySQL

select * from table where id = select max id in codeigniter

how select from table where datetime=date

Select fields from table with DISTINCT field

LINQ select data from many table

MySQL Best select query from two table

Select from table where the date is later then today

Select drop down from table array

How to select the following things from psql table?

SELECT * FROM MULTIPLE + DYNAMIC table_name

mysql select * from table group by id with rollup

Select rows by criteria in another data.frame without for loop in R

Select from Select