从Select查询中的另一个表中选择列

[英]Selecting Columns from another table inside a Select Query

本文翻译自 Carlo Luisito 查看原文 2016/04/06 53 sql-server/ sql

It seems to be the query that I have created is a little bit messy. Is there any other way to make it more readable?

看起来我创建的查询有点混乱。有没有其他方法使它更容易读?

Here's the query that I have created.

这是我创建的查询。

Select nullif((select count(*) from [casino_game].[dbo].[group_user] gu
        where gu.group_id = (select pt.group_id from [casino_game].[dbo].[promo_trigger] pt
        join [casino_game].[dbo].[promo_offer] po on po.trigger_id = pt.trigger_id
        where pt.name = f.promo_name
        group by pt.group_id)),0) as target_group,                           -- Number of customer uploaded to a free game program.

        (cast(count(*) as decimal(11,2)) / nullif((select count(*) from [casino_game].[dbo].[group_user] gu
        where gu.group_id = (select pt.group_id from [casino_game].[dbo].[promo_trigger] pt
        join [casino_game].[dbo].[promo_offer] po on po.trigger_id = pt.trigger_id
        where pt.name = f.promo_name
        group by pt.group_id)), 0)) * 100 as claim_rate from [data].[dbo].[testgame]

This query is working, but I need some suggestion if there will be another way to make this working and more readable.

这个查询是有效的，但是我需要一些建议，如果有其他方法可以使这个查询更有效、更可读的话。

2 个解决方案

#1

You can use OUTER APPLY to avoid using the same correlated sub-query twice

您可以使用OUTER APPLY避免使用相同的相关子查询两次

SELECT target_group, 
       ( Cast(Count(*) AS DECIMAL(11, 2)) / NULLIF(target_group, 0) ) * 100 AS claim_rate 
FROM   [data].[dbo].[testgame] 
       OUTER apply (SELECT Count(*) AS target_group 
                    FROM   [casino_game].[dbo].[group_user] gu 
                    WHERE  gu.group_id = (SELECT pt.group_id 
                                          FROM 
                           [casino_game].[dbo].[promo_trigger] pt 
                           JOIN [casino_game].[dbo].[promo_offer] 
                                po 
                             ON po.trigger_id = pt.trigger_id 
                                          WHERE  pt.NAME = f.promo_name 
                                          GROUP  BY pt.group_id)) ou

#2

I think the target table was group_user instead of testgame, and it must be a single row, no need for a join to many rows to testgame

我认为目标表是group_user而不是testgame，它必须是一行，不需要连接到许多行来进行testgame

select 
    COUNT(*) as target_group,  -- Number of customer uploaded to a free game program.
    -- It may cause the Divide by zero error if count(*) = 0
    CASE WHEN COUNT(*) > 0 THEN (cast((SELECT COUNT(*) as value FROM [data].[dbo].[testgame]) as decimal(11,2)) / COUNT(*)) * 100 END as claim_rate 
from 
    [casino_game].[dbo].[group_user] gu 
    inner join [casino_game].[dbo].[promo_trigger] pt on gu.group_id = pt.group_id
    inner join [casino_game].[dbo].[promo_offer] po on po.trigger_id = pt.trigger_id

智能推荐

注意！

本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2016/04/06/99bc09048f222d67744cfb0d6fdad81.html。

猜您在找

从[从另一个表的列]中选择SQL查询。 - SQL query SELECT FROM [value from column of another table] 根据存储为表中值的列从另一个表中选择值 - Selecting values from another table based on columns stored as value on the table 通过从另一个表MySQL查询中选择数据来更新表 - Update a table by selecting data from another table MySQL query 从一个表中选择一列，从另一个表中选择其他列 - select one column from one table and other columns from another table MySQL从一个表中选择所有列，而从另一个表中选择一些列 - MySQL Select all columns from one table and some from another table

赞助商链接