sql选择一个字段分为两列 [英] sql select a field into 2 columns

问题描述

我试图在同一张表上运行2个以下查询，并希望在2个不同的列中获得结果.

I am trying to run below 2 queries on the same table and hoping to get results in 2 different columns.

查询1:select ID as M from table where field = 1 返回:

1
2
3

查询2:select ID as N from table where field = 2 返回:

4
5
6

我的目标是获得

Column1 - Column2
-----------------
1            4
2            5
3            6

有什么建议吗?我正在使用SQL Server 2008 R2

Any suggestions? I am using SQL Server 2008 R2

谢谢

推荐答案

两个表之间的JOIN数据必须具有外键关系的主键.

There has to be a primary key to foreign key relationship to JOIN data between two tables.

这是关于关系代数和规范化的想法.否则，数据的相关性将毫无意义.

That is the idea about relational algebra and normalization. Otherwise, the correlation of the data is meaningless.

http://en.wikipedia.org/wiki/Database_normalization

CROSS JOIN将为您提供所有可能性. (1,4)，(1,5)，(1，6)...(3,6).我认为那不是您想要的.

The CROSS JOIN will give you all possibilities. (1,4), (1,5), (1, 6) ... (3,6). I do not think that is what you want.

您始终可以使用ROW_NUMBER()OVER()函数在两个表中生成代理键.在OVER()子句中以所需的方式对数据进行排序.但是，这仍然不是正常形式.

You can always use a ROW_NUMBER() OVER () function to generate a surrogate key in both tables. Order the data the way you want inside the OVER () clause. However, this is still not in any Normal form.

简而言之.为什么这样做?

快速测试数据库.使用非常规形式存储体育用品和家庭用品的产品.

Quick test database. Stores products from sporting goods and home goods using non-normal form.

SELECT的结果没有任何意义.

The results of the SELECT do not mean anything.

-- Just play
use tempdb;
go

-- Drop table
if object_id('abnormal_form') > 0
drop table abnormal_form
go

-- Create table
create table abnormal_form
(
    Id int,
    Category int,
    Name varchar(50)
);

-- Load store products
insert into abnormal_form values
(1, 1, 'Bike'),
(2, 1, 'Bat'),
(3, 1, 'Ball'),
(4, 2, 'Pot'),
(5, 2, 'Pan'),
(6, 2, 'Spoon');

-- Sporting Goods
select * from abnormal_form where Category = 1

-- Home Goods
select * from abnormal_form where Category = 2

-- Does not mean anything to me
select Id1, Id2 from
(select ROW_NUMBER () OVER (ORDER BY ID) AS Rid1, Id as Id1 
 from abnormal_form where Category = 1) as s
join
(select ROW_NUMBER () OVER (ORDER BY ID) AS Rid2, Id as Id2 
 from abnormal_form where Category = 2) as h
on s.Rid1 = h.Rid2

我们绝对需要用户提供更多信息.

We definitely need more information from the user.

这篇关于sql选择一个字段分为两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！