如何在一个表中显示两个查询的结果

问题描述：

我的select查询，我已经删除t0.brandID = null它并不重要，只需要在一个表中获取查询结果。如何在一个表中显示两个查询的结果

SELECT 
    t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus, t0.AddedBy 
FROM 
    brands t0 
WHERE 
    t0.brandName = 'budwieser' 

SELECT 
    AVG(CAST (brandID AS bigint)) AS brandID_AVERAGE, 
    MIN(CAST (brandID AS bigint)) as branid_min, 
    MAX(CAST (brandID AS bigint)) as brandid_max, 
    COUNT(CAST (brandID AS bigint)) as brandid_count 
FROM 
    (SELECT 
     t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus, t0.AddedBy 
    FROM 
     brands t0 
    WHERE 
     t0.brandID = null OR t0.brandName = 'budwieser') temptable

上述查询的结果是在两个不同的表，如下图：

brandid brandname  cdt      udt  brandstatus added by 

    8 budwieser 2013-11-14 16:26:43.913 2014-02-12 19:26:43.913 1 8 
    18 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 
    23 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 
    37 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 
    63 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 
    82 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 
    92 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1

和

我想显示如下结果：

brandid brandname cdt    udt brandstatus addedby branid_average brandid_min brandid_max branid_count 
    8 budwieser 2013-11-14  2014-02-12 1 8   46    8    92    7 
    18 budwieser 2013-11-15  2013-11-15 1 1   null   null   null   null 
    ........................................................  null   null   null   null 
    --------------------------------------------------------  null   null   null   null

您不能使用常规的相等运算符检查'NULL' - 你需要使用'to.BrandID IS NULL'（使用' IS NULL'或'IS NOT NULL' - 不是'= null'）。 –

你能解释为什么你的预期产量有8个百威啤酒记录的计算值，而不是18个百威啤酒记录？阅读你的问题，我期待在百威啤酒的所有记录中都有相同的价值观。你如何关联/配对2个查询的结果？ –

谢谢，我必须计算选定记录的平均值，最小值，最大值。在我的表格中，我在Branid's（8,18,23，...，92）有budwiser条目，平均值为（8 + 18 + 23 + ..）/ 7 = 46.07，接近46.选定记录的最小brandid是8，所选记录的最大brandid是92，记录数是7.我必须显示这些值以及选定的值，如branid，brandname，..，brandid_average，branid_min，brandid_max，brandid_count。这是测试场景，实际使用将用于计算不同逆变器的电压。这将作为报告导出为excel。 – Tan

答

在这种情况下，两个表结果需要结合起来，可以通过从两个表中选择来简单完成。例如table1，table2。

Table1 has columns id,name,age 
table2 has columns someid,group,gender

的结果显示如下格式简单的查询是：

select t1.*,t2.* from table1 t1,table2 t2 

id name age someid group gender 
1 one 1 10  5  M

同样，对于上述问题，因为没有物理表存在，我们要声明一个表，并保存值到临时表，然后简单地从两个表中选择将返回问题所需的结果。

 drop table #mytemptable --- droping the temporary table if exists 
//select query 
     select AVG(CAST (brandID AS bigint)) AS brandID_AVERAGE, 
     min(CAST (brandID AS bigint)) as branid_min, 
     MAX(CAST (brandID AS bigint)) as brandid_max, 
     COUNT(CAST (brandID AS bigint)) as brandid_count 
into #mytemptable ---//Here inserting the selected values from below query to temporary table 
    from 
     (SELECT t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus, t0.AddedBy 

     FROM brands t0 
     Where t0. brandID=null OR t0. brandName='budwieser' 

     ) temptable 
     //simple select from both tables 
     SELECT t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus, t0.AddedBy ,t2.* 
     from brands t0,#mytemptable t2 
     where t0.brandName='budwieser'

预期的结果将是：

brandID brandName cdt udt brandstatus AddedBy brandID_AVERAGE branid_min brandid_max brandid_count 
8 budwieser 2013-11-14 16:26:43.913 2014-02-12 19:26:43.913 1 8 46 8 92 7 
18 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 46 8 92 7 
23 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 46 8 92 7 
37 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 46 8 92 7 
63 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 46 8 92 7 
82 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 46 8 92 7 
92 budwieser 2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1 1 46 8 92 7

答

SELECT t0.brandID, 
     t0.brandName, 
     t0.cdt, 
     t0.udt, 
     t0.brandstatus, 
     t0.AddedBy, 
     AVG(CAST(brandID AS BIGINT)) OVER(PARTITION BY t0.brandName) AS brandID_AVERAGE, 
     MIN(CAST(brandID AS BIGINT)) OVER(PARTITION BY t0.brandName) AS branid_min, 
     MAX(CAST(brandID AS BIGINT)) OVER(PARTITION BY t0.brandName) AS brandid_max, 
     COUNT(CAST(brandID AS BIGINT)) OVER(PARTITION BY t0.brandName) AS brandid_count 
FROM brands t0 
WHERE t0.brandName = 'budwieser'

如果我选择两个日期而不是“Where t0.brandname ='budwieser'”to“where t0.cdt> ='2013-11-14'and t0.cdt Tan

如何在一个表中显示两个查询的结果

相关推荐