sql—通过将表的四舍五入合并列的结果放入不同表的另一列中来创建视图

b5buobof 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(251)

下面是我试图合并成一个结果的表格。


**MainTable**

            --------------------------------------------------------------
            ID    Col1    Col2    Col3    Col4   ClassType     UserName
            --------------------------------------------------------------
            1      1       0      NULL     1       A           Kate
            2      0       1       1       1       C           Leo
            3      1       1       0       0       D           Mark
            4      1       0       1       1       B           Cathy
            5      0      NULL    NULL     1       A           Lex
            6      1       1      NULL     1       B           Dwight
            7      0       0      NULL     0       C           Jim
            --------------------------------------------------------------

**ClassA Table**

            --------------------------------------------------------------
            ID    Val1    Val2   Val3    Val4  ClassType     UserName
            --------------------------------------------------------------
            1      1       1       1       1       A           Kate
            2      0       1       1       1       A           Lex
            --------------------------------------------------------------

**ClassB Table**

            --------------------------------------------------------------
            ID    Val1    Val2   Val3    Val4  ClassType     UserName
            --------------------------------------------------------------
            1      1       0       1       1       B           Cathy
            2      0       1       1       1       B           Dwight
            --------------------------------------------------------------

**ClassC Table**

            --------------------------------------------------------------
            ID    Val1    Val2   Val3    Val4  ClassType     UserName
            --------------------------------------------------------------
            1      1       0       1       1       C           Leo
            2      0       1       1       1       C           Jim
            --------------------------------------------------------------

**ClassD Table**

            --------------------------------------------------------------
            ID    Val1    Val2   Val3    Val4  ClassType     UserName
            --------------------------------------------------------------
            1      1       0       1       1       C           Mark
            --------------------------------------------------------------

我想要达到的是这样的目标

Select ID, Col1, Col2 , Col3, Col4, ClassType, (select Round((100.00*(COALESCE(Val1,0) +
COALESCE(Val2,0) +
COALESCE(Val3,0) +
COALESCE(Val4,0)) / 4), 0) as TotalVal from @TableName where username = @Username) as TotalVal , UserName
Where UserName = @Username And ClassType = @ClassType

我可以把每个类表中的取整val列的总数放在 as TotalVal 取决于什么 ClassType 在 MainTable 我要找的。我试着在查询中包含所有的表，但是我认为这样做效率很低，因为我会调用所有的表，即使我不需要它，因为我只调用1表，这取决于类类型，我认为如果需要“if condition”或“case”会更好。另外，我将把上面的select语句或查询作为一个视图。但它不能正常工作。
我应该如何使用上面的查询来获得下面的结果？或者也许有更有效的方法来实现以下结果使用不同的方法？


**Result of Query**

            --------------------------------------------------------------------------
            ID    Col1    Col2    Col3    Col4   ClassType   TotalVal     UserName
            --------------------------------------------------------------------------
            1      1       0      NULL     1       A          100.00         Kate
            --------------------------------------------------------------------------

我有点新的sql和我不知道的术语，所以请原谅我。如果我的问题有错，请纠正我。我想学习。谢谢您。

sql sql-server

来源：https://stackoverflow.com/questions/63065947/creating-a-view-by-putting-results-of-rounded-coalesce-columns-of-tables-in-a

1条答案

按热度按时间

wgmfuz8q1#

尝试将各种表合并成这样的数据集。。。

DECLARE
    @TableName varchar(50) = 'ClassA',
    @UserName varchar(50) = 'Kate',
    @ClassType varchar(50) = 'A'

;WITH MainTable AS (
    SELECT * FROM (VALUES
            (1, 1, 0, NULL, 1, 'A', 'Kate'),
            (2, 0, 1, 1, 1, 'C', 'Leo'),
            (3, 1, 1, 0, 0, 'D', 'Mark'),
            (4, 1, 0, 1, 1, 'B', 'Cathy'),
            (5, 0, NULL, NULL, 1, 'A', 'Lex'),
            (6, 1, 1, NULL, 1, 'B', 'Dwight'),
            (7, 0, 0, NULL, 0, 'C', 'Jim')
    ) S(ID, Col1, Col2, Col3, Col4, ClassType, UserName)
), ClassA AS (
    SELECT * FROM (VALUES
            (1, 1, 1, 1, 1, 'A', 'Kate'),
            (2, 0, 1, 1, 1, 'A', 'Lex')
    ) S(ID, Val1, Val2, Val3, Val4, ClassType, UserName)
), ClassB AS (
    SELECT * FROM (VALUES
            (1, 1, 0, 1, 1, 'B', 'Cathy'),
            (2, 0, 1, 1, 1, 'B', 'Dwight')
    ) S(ID, Val1, Val2, Val3, Val4, ClassType, UserName)
), ClassC AS (
    SELECT * FROM (VALUES
            (1, 1, 0, 1, 1, 'C', 'Leo'),
            (2, 0, 0, 1, 1, 'C', 'Jim')
    ) S(ID, Val1, Val2, Val3, Val4, ClassType, UserName)
), ClassD AS (
    SELECT * FROM (VALUES
            (1, 1, 0, 1, 1, 'D', 'Mark')
    ) S(ID, Val1, Val2, Val3, Val4, ClassType, UserName)
)

SELECT
    M.ID, M.Col1, M.Col2 , M.Col3, M.Col4, M.ClassType, U.TotalVal
FROM
    MainTable M
    JOIN (
        SELECT UserName, 'ClassA' TableName, ROUND((25 * (ISNULL(Val1, 0) + ISNULL(Val2, 0) + ISNULL(Val3, 0) + ISNULL(Val4, 0))), 0) TotalVal FROM ClassA UNION ALL
        SELECT UserName, 'ClassB' TableName, ROUND((25 * (ISNULL(Val1, 0) + ISNULL(Val2, 0) + ISNULL(Val3, 0) + ISNULL(Val4, 0))), 0) TotalVal FROM ClassB UNION ALL
        SELECT UserName, 'ClassC' TableName, ROUND((25 * (ISNULL(Val1, 0) + ISNULL(Val2, 0) + ISNULL(Val3, 0) + ISNULL(Val4, 0))), 0) TotalVal FROM ClassC UNION ALL
        SELECT UserName, 'ClassD' TableName, ROUND((25 * (ISNULL(Val1, 0) + ISNULL(Val2, 0) + ISNULL(Val3, 0) + ISNULL(Val4, 0))), 0) TotalVal FROM ClassD
    ) U ON
        U.UserName = M.UserName AND
        U.TableName = @TableName
WHERE

    M.UserName = @UserName AND
    M.ClassType = @ClassType

坦率地说，看看这些数据，我想你不会用 TableName 甚至加入工会 ClassType 像这样(用这个语句替换上一个select语句）

SELECT
    M.ID, M.Col1, M.Col2 , M.Col3, M.Col4, M.ClassType, U.TotalVal
FROM
    MainTable M
    JOIN (
        SELECT UserName, ClassType, ROUND((25 * (ISNULL(Val1, 0) + ISNULL(Val2, 0) + ISNULL(Val3, 0) + ISNULL(Val4, 0))), 0) TotalVal FROM ClassA UNION ALL
        SELECT UserName, ClassType, ROUND((25 * (ISNULL(Val1, 0) + ISNULL(Val2, 0) + ISNULL(Val3, 0) + ISNULL(Val4, 0))), 0) TotalVal FROM ClassB UNION ALL
        SELECT UserName, ClassType, ROUND((25 * (ISNULL(Val1, 0) + ISNULL(Val2, 0) + ISNULL(Val3, 0) + ISNULL(Val4, 0))), 0) TotalVal FROM ClassC UNION ALL
        SELECT UserName, ClassType, ROUND((25 * (ISNULL(Val1, 0) + ISNULL(Val2, 0) + ISNULL(Val3, 0) + ISNULL(Val4, 0))), 0) TotalVal FROM ClassD
    ) U ON
        U.UserName = M.UserName AND
        U.ClassType = M.ClassType
WHERE

    M.UserName = @UserName AND
    M.ClassType = @ClassType

赞(0）回复(0）举报 2021-07-24

我来回答

sql—通过将表的四舍五入合并列的结果放入不同表的另一列中来创建视图

1条答案

相关问题

热门标签

最新问答