SQL从两列和两个表追加不同的值 [英] SQL append distinct values from two columns and two tables

问题描述

我正在尝试创建从两列中获取不同值并将其追加的SQL代码。这样，我的意思是下表：

I'm trying to create SQL code that takes the distinct values from two columns and "appends" them. By that, I mean that that the following table:

Account   Article
-----------------
   1        1
   1        2
   2        3

应产生以下结果：

Account   Article
-----------------
   1        1
   1        2
   1        3
   2        1
   2        2
   2        3

我正在使用一个联合表在两个表中进行此操作，因此我们的想法是同时获得两个唯一帐号的所有组合两个表格中所有唯一商品编号的表格。我希望有一个条款将两个表的订购日期限制在一年前。

I'm doing this from two tables using a union, so the idea is to get all combination of all unique account numbers in both tables with all unique article numbers in both tables. And I want a clause that limits both tables to a order date later then one year ago.

到目前为止，我有：

Select Distinct 
    "Tra.".cus_outnum As "account number", 
    "Tra.".artnum As "article number"
From 
    (table1) "Tra." 
Where 
    "Tra.".invdat >= DATEADD(year, -1, GETDATE())

Union

Select Distinct 
    "Sal.".outnum As "account number", 
    "Sal.".artnum As "article number"
From 
    (table2) "Sal."  
Where 
    "Sal.".deldat>= DATEADD(year, -1, GETDATE())

问题在于，它仅在帐户和商品同时存在的情况下为我提供组合。我对使用with语句感到厌倦：

Problem is that it only gives me the combination where both account and article exist. I have unsuccessfully tired to do it with a with statement:

WITH temp1 AS 
(
     Select distinct cus_outnum 
     From table1
), temp2 AS
(
     Select distinct artnum 
     From table1
)
SELECT cus_outnum,artnum
FROM temp1, temp2, table1

蚂蚁帮助非常必要！

推荐答案

这给出了预期的结果：

with cte1 as (Select distinct account from test)
,cte2 as (Select distinct article from test)
Select * from cte1 cross join cte2

模式：

Create table test(account int, article int);
Insert into test values(1,1);
Insert into test values(1,2);
Insert into test values(2,3);

这篇关于SQL从两列和两个表追加不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！