如何从SQL Server中的值列表中进行选择

I have very simple problem that I can't solve. I need to do something like this:

我有一个我无法解决的简单问题。我需要这样做:

select distinct * from (1, 1, 1, 2, 5, 1, 6).

Anybody can help??

任何人都可以帮忙吗? ?

Edit

编辑

The data comes as a text file from one of our clients. It's totally unformatted (it's a single, very long line of text), but it may be possible to do so in Excel. But it's not practical for me, because I will need to use these values in my sql query. It's not convenient to do so every time I need to run a query.

数据来自于我们的一个客户端的文本文件。它完全没有格式(只是一行很长的文字)，但是在Excel中也可以这样做。但这对我来说并不实用，因为我需要在sql查询中使用这些值。每次需要运行查询时，这样做并不方便。

11 个解决方案

#1

Simplest way to get the distinct values of a long list of comma delimited text would be to use a find an replace with UNION to get the distinct values.

获得逗号分隔文本的长列表的不同值的最简单方法是使用find替换为UNION来获得不同的值。

SELECT 1
UNION SELECT 1
UNION SELECT 1
UNION SELECT 2
UNION SELECT 5
UNION SELECT 1
UNION SELECT 6

Applied to your long line of comma delimited text

应用于逗号分隔的长行文本

Find and replace every comma with UNION SELECT
用UNION SELECT查找并替换每个逗号
Add a SELECT in front of the statement
在语句前面添加一个SELECT

You now should have a working query

现在应该有一个有效的查询

#2

274

Available only on SQL Server 2008 and over is row-constructor in this form:
You could use

只能在SQL Server 2008和以上版本中使用这种形式的行构造函数:您可以使用

SELECT DISTINCT * FROM (VALUES (1), (1), (1), (2), (5), (1), (6)) AS X(a)

Many wrote about, among them:

许多人写过，其中有:

[MS official] https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql
(官方女士)https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql
http://www.sql-server-helper.com/sql-server-2008/row-value-constructor-as-derived-table.aspx
http://www.sql-server-helper.com/sql-server-2008/row-value-constructor-as-derived-table.aspx

#3

Have you tried using the following syntax?

您尝试过使用以下语法吗?

select * from (values (1), (2), (3), (4), (5)) numbers(number)

#4

In general :

一般来说:

SELECT 
  DISTINCT 
      FieldName1, FieldName2, ..., FieldNameN
FROM
  (
    Values
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN )
  ) AS TempTableName ( FieldName1, FieldName2, ..., FieldNameN )

In your case :

在你的例子:

Select 
  distinct
  TempTableName.Field1 
From 
  (
  VALUES
    (1), 
    (1), 
    (1), 
    (2), 
    (5), 
    (1), 
    (6)
  ) AS TempTableName (Field1)

#5

If you want to select only certain values from a single table you can try this

如果您想从单个表中选择某些值，您可以尝试这个方法。

select distinct(*) from table_name where table_field in (1,1,2,3,4,5)

eg:

例如:

select first_name,phone_number from telephone_list where district id in (1,2,5,7,8,9)

if you want to select from multiple tables then you must go for UNION.

如果您想从多个表中进行选择，那么您必须选择UNION。

If you just want to select the values 1, 1, 1, 2, 5, 1, 6 then you must do this

如果你想要选择值1 1 2 5 1 6，那么你必须这样做。

select 1 
union select 1 
union select 1 
union select 2 
union select 5 
union select 1 
union select 6

#6

PostgreSQL gives you 2 ways of doing this:

PostgreSQL提供了两种方法:

SELECT DISTINCT * FROM (VALUES('a'),('b'),('a'),('v')) AS tbl(col1)

或

SELECT DISTINCT * FROM (select unnest(array['a','b', 'a','v'])) AS tbl(col1)

using array approach you can also do something like this:

使用数组方法，您还可以做以下事情:

SELECT DISTINCT * FROM (select unnest(string_to_array('a;b;c;d;e;f;a;b;d', ';'))) AS tbl(col1)

#7

This works on SQL Server 2005 and if there is maximal number:

这适用于SQL Server 2005，如果有最大数量:

SELECT * 
FROM
  (SELECT ROW_NUMBER() OVER(ORDER BY a.id) NUMBER
  FROM syscomments a
  CROSS JOIN syscomments b) c
WHERE c.NUMBER IN (1,4,6,7,9)

#8

If you need an array, separate the array columns with a comma:

如果需要数组，请使用逗号分隔数组列:

SELECT * FROM (VALUES('WOMENS'),('MENS'),('CHILDRENS')) as X([Attribute])
,(VALUES(742),(318)) AS z([StoreID])

#9

Another way that you can use is a query like this:

你可以使用的另一种方式是这样的查询:

SELECT DISTINCT
    LTRIM(m.n.value('.[1]','varchar(8000)')) as columnName
FROM 
    (SELECT CAST('<XMLRoot><RowData>' + REPLACE(t.val,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
     FROM (SELECT '1, 1, 1, 2, 5, 1, 6') AS t(val)
    ) dt
  CROSS APPLY 
    x.nodes('/XMLRoot/RowData') m(n);

#10

-1

A technique that has worked for me is to query a table that you know has a large amount of records in it, including just the Row_Number field in your result

对我有用的一种技术是查询一个您知道有大量记录的表，其中包括您的结果中的Row_Number字段。

Select Top 10000 Row_Number() OVER (Order by fieldintable) As 'recnum' From largetable

will return a result set of 10000 records from 1 to 10000, use this within another query to give you the desired results

是否会从1到10000条返回10000条记录的结果集，并在另一个查询中使用该结果以获得所需的结果

#11

-1

Use the SQL In function

在函数中使用SQL

Something like this:

是这样的:

SELECT * FROM mytable WHERE:
"VALUE" In (1,2,3,7,90,500)

Works a treat in ArcGIS

在ArcGIS中工作

#1