首页 \ 问答 \ 使用Case和Subquery分组(Group by with Case and Subquery)

使用Case和Subquery分组(Group by with Case and Subquery)

 使用以下SQL执行分组时遇到问题：  
SELECT 
    CASE 
         WHEN  t = '11' then 'V' 
         WHEN (t = '20' OR t = '25') THEN 'S' 
    END as [ty],

    CONVERT(CHAR(3), DateEx, 101) + CONVERT(CHAR(4), DateEx, 120) as [Fecha], COUNT(*) as [Total],  
    SUM (case when  CodLastEstado in (3,4,5,6,7,10,11,12,13) then 1 else 0 end ) as [C1],
    SUM (case when  CodLastEstado in (4,5,6) then 1 else 0 end ) as [C2],
    (
    select COUNT(*)
    from (SELECT t.Line, t.VTO, t.SubTypo
          FROM (SELECT c.Line, CONVERT(CHAR(3), c.DateEx, 101) + CONVERT(CHAR(4), c.DateEx, 120) as [VTO],
                       ROW_NUMBER() OVER (PARTITION BY a.numid ORDER BY a.FechaAccion desc) as Fila, SubTypo
                FROM Acciones a, contratos c
                WHERE A.numid = c.numid
                      AND (c.CodLastEstado in (10,11) and a.IDCodTipoEstado in (4,5,6)) 
                      and c.Brand = 'mouse'             
               ) t
          where t.Fila = 1
         )as q   
    where q.VTO = CONVERT(CHAR(3), contratos.DateEx, 101) + CONVERT(CHAR(4), contratos.DateEx, 120)
    and q.SubTypo = contratos.SubTypo
    ) as [CHco],    
    SUM (case when  CodLastSubEstado in (56,46,36,3,13,24) then 1 else 0 end ) as [N],
 FROM contratos
WHERE Brand = 'MINI' AND DateEx between '01/11/2014' and '30/11/2014'
GROUP BY SubTypo,
CONVERT(CHAR(3), contratos.DateEx, 101) + CONVERT(CHAR(4), contratos.DateEx, 120)
 
 语法正确，显示如下：  
ty||Total||C1||C2||CHco||N

S || 120 ||5 ||50||  3 ||0
S ||   1 ||0 ||0 ||  1 ||0
V ||   3 ||0 ||0 ||  0 ||0
 
 我尝试了很多东西，如下所示，但我没有，有什么想法？  
ty||Total||C1||C2||CHco||N

S || 121 ||5 ||50||  4 ||0
V ||   3 ||0 ||0 ||  0 ||0

I'm having problems when performing a grouping with the following SQL: 
SELECT 
    CASE 
         WHEN  t = '11' then 'V' 
         WHEN (t = '20' OR t = '25') THEN 'S' 
    END as [ty],

    CONVERT(CHAR(3), DateEx, 101) + CONVERT(CHAR(4), DateEx, 120) as [Fecha], COUNT(*) as [Total],  
    SUM (case when  CodLastEstado in (3,4,5,6,7,10,11,12,13) then 1 else 0 end ) as [C1],
    SUM (case when  CodLastEstado in (4,5,6) then 1 else 0 end ) as [C2],
    (
    select COUNT(*)
    from (SELECT t.Line, t.VTO, t.SubTypo
          FROM (SELECT c.Line, CONVERT(CHAR(3), c.DateEx, 101) + CONVERT(CHAR(4), c.DateEx, 120) as [VTO],
                       ROW_NUMBER() OVER (PARTITION BY a.numid ORDER BY a.FechaAccion desc) as Fila, SubTypo
                FROM Acciones a, contratos c
                WHERE A.numid = c.numid
                      AND (c.CodLastEstado in (10,11) and a.IDCodTipoEstado in (4,5,6)) 
                      and c.Brand = 'mouse'             
               ) t
          where t.Fila = 1
         )as q   
    where q.VTO = CONVERT(CHAR(3), contratos.DateEx, 101) + CONVERT(CHAR(4), contratos.DateEx, 120)
    and q.SubTypo = contratos.SubTypo
    ) as [CHco],    
    SUM (case when  CodLastSubEstado in (56,46,36,3,13,24) then 1 else 0 end ) as [N],
 FROM contratos
WHERE Brand = 'MINI' AND DateEx between '01/11/2014' and '30/11/2014'
GROUP BY SubTypo,
CONVERT(CHAR(3), contratos.DateEx, 101) + CONVERT(CHAR(4), contratos.DateEx, 120)
 
The syntax is correct, showing the following: 
ty||Total||C1||C2||CHco||N

S || 120 ||5 ||50||  3 ||0
S ||   1 ||0 ||0 ||  1 ||0
V ||   3 ||0 ||0 ||  0 ||0
 
I tried many things to be displayed as follows, but I have not got, any ideas? 
ty||Total||C1||C2||CHco||N

S || 121 ||5 ||50||  4 ||0
V ||   3 ||0 ||0 ||  0 ||0

原文：

更新时间：2022-03-25 09:03

最满意答案

 一些技巧：  
 
  {}括号是对象 ，您可以访问object.property等object.property 。  
  []括号是数组 ，您可以通过索引array[index]访问元素。  
 
 在您的特定情况下，这应该这样做：  
var objects_array = JSON.parse(data); // This is an array of objects.
var total = objects_array.length;

for (var i = 0; i < total; i++) {
    var obj = objects_array[i]; // This is one object from the array.
    console.log( obj.latitude + ',' + obj.longitude ); // We access object properties using a dot.
}

Some tips: 
 
 {} parentheses are objects, you access properties like object.property. 
 [] parentheses are arrays, you access elements by indexes array[index]. 
 
In your particular case, this should do it: 
var objects_array = JSON.parse(data); // This is an array of objects.
var total = objects_array.length;

for (var i = 0; i < total; i++) {
    var obj = objects_array[i]; // This is one object from the array.
    console.log( obj.latitude + ',' + obj.longitude ); // We access object properties using a dot.
}

无法循环浏览JSON数据(Can't loop through JSON data)[2022-06-22]

你可以使用Array.prototype.forEach（）并做： const data = {"records": [{"id": "recGWUWqwjUNLpekA","fields": {"playerSprints": 12,"playerDistanceCovered_km": 6.23},"createdTime": "2018-03-22T18:16:56.000Z"},{"id": "recx5pMFpxnRwR4La","fields": {"playerSprints": 12,"pl ...

我如何循环json数据？(How can i loop through json data?)[2024-03-03]

基本上你可以在这里使用3个机会： Array.forEach（如下所示）通常用于循环（ for(let t = 0; t < data.length; t++) { ... } ）循环的ES7（ for(const el of data) { ... } ）假设你有这样的数据： const data = [ { "FIELD1": "name", "FIELD2": "id"}, { "FIELD1": "abc", "FIELD2": "12"}]; 你可以循环它： data.forEach(el ...

使用for..in循环访问JSON数据(Using the for..in loop to access JSON data)[2023-12-02]

if (info.hasOwnProperty(key)) 你不需要那个检查。你的值是普通的对象，没有人足够愚蠢地扩展具有可枚举属性的Object.prototype 。 for (prop in person) 这似乎是不必要的，如果没有错的话。您只想访问gifts属性，因此无需枚举name ， address和gifts 。同样的事情 for (var key in info) 你只想得到donors并迭代他们 - 对象甚至没有任何其他属性。我的JSON不必以这种方式构建然后我建议使用数 ...

如何使用json数据进行循环？(How to make the loop with json data?)[2021-12-31]

一些技巧： {}括号是对象，您可以访问object.property等object.property 。 []括号是数组，您可以通过索引array[index]访问元素。在您的特定情况下，这应该这样做： var objects_array = JSON.parse(data); // This is an array of objects. var total = objects_array.length; for (var i = 0; i < total; i++) { var obj ...

Json“for”循环(Json “for” loop)[2022-08-11]

$.getJSON是异步的，处理程序将在收到响应时被调用。但是for-loop不会等待处理程序被调用！创建一个inner-function并传递i作为参数的值，因为i值对于inner-function是持久的。 $(function() { for (var i = 1; i < 4; i++) { var tobeCalled = function(i) { var box = "#t" + i; var id = "dQw4w9WgXcQ"; ...

使用Case和Subquery分组(Group by with Case and Subquery)

最满意答案

相关问答

使用for循环时如何防止json数据反转？(How do I prevent json data getting reversed when using for loop?)[2019-10-30]

循环通过json(Loop through json)[2019-08-08]

在循环中解析json数据(parse json data inside while loop)[2022-06-03]

jquery循环Json数据使用$ .each(jquery loop on Json data using $.each)[2022-06-02]

jQuery $ .each循环和json数据(jQuery $.each loop and json data)[2023-06-28]

无法循环浏览JSON数据(Can't loop through JSON data)[2022-06-22]

我如何循环json数据？(How can i loop through json data?)[2024-03-03]

使用for..in循环访问JSON数据(Using the for..in loop to access JSON data)[2023-12-02]

如何使用json数据进行循环？(How to make the loop with json data?)[2021-12-31]

Json“for”循环(Json “for” loop)[2022-08-11]

相关文章

最新问答