TheDemonLord's questions

数据库是 MariaDB 10.3.25

我有 2 列相关：

用户 ID 和 LastUpdate

UserID 由 user@domain LastUpdate 是一个日期字段。

这是我的问题 - 我有当前的查询：

select a.UserID, 
substring_index(a.UserID, '@', -1), 
max(a.lastupdate) 
from MyTable a 
group by a.UserID
having max(a.lastupdate) < '2020-03-31' 

这显示了一年多内未更新的所有用户和域。但是，我要考虑以下情况：

UserID LastUpdate
[email protected] 2020-08-16
[email protected] 2019-05-16
[email protected] 2021-05-05

使用当前查询，将捕获 [email protected]，因此域 A.com 将被捕获为未使用，但用户 [email protected] 仍处于活动状态 - 我想做一个 Max(a.lastupdate ) 但将其分组， substring_index(a.UserID, '@', -1) 但也会吐出该域的所有用户。我敢肯定，答案让我眼前一亮……

这是在 MSSQL/Azure SQL 上。

我有以下销售表：

我正在尝试获取显示每个 SKU、订单总数（非常简单）和包含该 SKU 的订单的订单项总数的输出。

所以对于第一部分：

select SKU, count(distinct(sales_order_id) as "Order_Count"  from Table T
group by SKU

很简单，但是第二部分是我的头脑 -

所需的输出如下所示：

如何计算每个包含 SKU 的订单的 Line_item_ID 总数？理想情况下，在单个查询中没有大量疯狂的连接或子查询。

我在 Azure SQL DB 中有下表，其中包含我试图求和的重复值。

逻辑如下：如果 PaymentID 唯一，则 Sum Payment，如果 creditID 唯一，则 sum credit，如果 debitid 唯一，则 sum debit。并采用 max(source) 这个想法是得到一个单行，每个 ID 具有不同的 ID 总和值。

例如：

158464166 | -185.15 | 0 | -3.45 | 抄送 | 5910551 | 5910548

（在上面 - 我已经采取了 min(paymentid) 让它看起来更好

请注意，虽然在上面的代码片段中，Creditid 和 Debitid 只有一个重复的 ID，但它们可能有不同的 ID，因此任何代码都必须能够处理它。PaymentID 将始终是唯一的。

付款、贷记和借记的值也可能不是唯一的（例如，一个 ID 支付了两次 50 美元），因此我们不能对付款进行分组。

我做到了这一点：

SELECT id, sum(payment), sum(credit), sum(debit), max(source), creditid, debitid  
FROM (  
  SELECT *,  
         COUNT(*) OVER (PARTITION BY id) AS cnt  
  FROM Temp_Payment) AS t  
WHERE t.cnt > 1  

group by id, creditid, debitid

但它没有给出预期的结果。

我有一个 Azure SQL 数据库，一个包含 3 个相关列的表：品牌、ID 和月/年，还有一个临时表，它简单地列出了带有 ID 的月/年。

该表显示了该给定月份的所有活动 ID 的列表。

Month/Year 是一个日期戳列，日期为月份的第一个。ID 列只是一个 ID。

目标是有一个输出，每个月将显示本月活跃但下个月不活跃的所有记录的计数（流失）以及上个月不活跃但活跃的 ID在这个月（新），并按品牌和月份分组，所以：

品牌 | 流失 | 新 | 第 1 个月
| 10 | 12 | 2019-12-01
2 | 11 | 9 | 2019-12-01

我还应该注意到 Table1 有 1900 万行。

我尝试了以下代码：

Declare @Id int
declare @my date

set @Id = 300
set @my = '2019-12-01'

select t1.[brand],
count(c.[ID]) as [churn],
count(n.[ID]) as [new],
t1.[month/year]
from Table1 T1

left join Table1 c on
c.[ID] = t1.[ID] and c.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id +1)))
left join Table1 n on
n.[ID] = T1.[ID] and n.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id -1)))

where t1.[Month/Year] = @my
group by t1.[Brand], t1.[Month/Year]

尽管它产生了输出，但我认为它不正确，而且花了很长时间，这可能是由于我在联接中自由使用了子查询。

我的问题是 2 倍 - 有人可以帮助以更好的方式计算行数并加入它们吗？有没有更好的方法可以在没有这么多子查询的情况下做到这一点？

这是我的问题，源数据库是 Azure MSSQL - 我有一张表，列出了所有已售出的订阅。相关列是：

账号、订阅组、购买日期、取消日期

取消日期包含 NULL 值（对于尚未取消的订阅）。一个帐户可能有多个订阅，跨越多个订阅组。

样本数据为：

帐号 | 订阅群 | 购买日期 | 取消日期 |

1 | 一个 | 2013 年 2 月 4 日 | 2014 年 2 月 4 日 |
1 | 一个 | 2013 年 2 月 4 日 | 空 |
1 | 乙| 2013 年 2 月 4 日 | 空 |
2 | C | 2018 年 5 月 7 日 | 2020 年 5 月 8 日 |
3 | C | 20/3/2020 | 空 |
3 | C | 2020 年 4 月 8 日 | 空 |

等等。

我正在计算每个客户和每个月的订阅组数量 - 所以输出看起来像这样：

客户 | A 的计数 | B 的计数 | C 计数 | 月/年 |
1 | 1 | 1 | 0 | 2020 年 10 月 |
3 | 0 | 0 | 2 | 2020 年 10 月 |
1 | 1 | 1 | 0 | 09/2020 |
3 | 0 | 0 | 2 | 09/2020 |
1 | 1 | 1 | 0 | 08/2020 |
2 | 0 | 0 | 1 | 08/2020 |
3 | 0 | 0 | 2 | 08/2020 |

我可以为给定月份编写查询：

select t.[account number],
COUNT(CASE WHEN t.[Subscription group] = A then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN t.[Subscription group] = B then 1 ELSE NULL END ) 作为 "Count of B",
COUNT(CASE WHEN t.[Subscription group] = C then 1 ELSE NULL END) as "Count of C",
'09/2020' as 'Month/Year'
from table t
where (t .[Purchase Date] <= '30/09/2020' and (t.[Cancellation Date] is null or t.[Cancellation Date] > '01/10/2020') )
按 t.[Account Number] 分组， [月/年]

但是，我希望有人可以帮助我修改上述内容，以便它将运行所有月份（基于购买日期的 MIN 和取消日期的 MAX）

数据库是 Postgres。

我有一个问题，我有一个包含以下列的表：ID、Product_ID Customer_ID、Price 和 Item_Type。

此表在 Product_ID 上连接到另一个表。对于给定的产品 ID，给定的商品类型（新品、续订、赎回等）有默认价格（其中 Customer_ID 为空白），还可能有一个客户特定的价目表（其中 Customer_ID 加入到客户表中）。

问题是该数据库维护不善。对于 Item_Type，我们对此期感兴趣的有 2 个价格：续订和续订。有些价格有续订价格，有些价格有续订价格，有些则两者兼有。

select p.id, pr.customer_id, p.period, pr.price, pr.item_type
from products p
right join prices pr on
pr.product_id = p.id and pr.period = p.period and pr.customer_id is null
where p.status = 'PURCHASED'

例如，这会产生以下内容：

121751  8407508 12 29.95 RENEW
121751  8407508 12 35.95 RENEWAL

原因是该产品有 2 个不同的续订和续订价格 - 查看前端应用程序代码 - 如果有 item_type 'RENEWAL' 则使用此价格，如果没有则使用 'RENEW' 价格。

我尝试在 select 和 where 子句中使用 case 语句：

case pr.item_type 
    when 'RENEWAL' then pr.price
    when 'RENEW' then pr.price
    end

但我仍然得到两行 - 我不确定如何进行条件和分层查找，例如，如果条件 A 匹配，则返回与条件 A 匹配的行，如果未找到匹配项，则条件 B 匹配，然后返回该行匹配条件 B 等。

我需要将两个表中的值相乘并对结果求和。该数据库是 Postgres 或 MSSQL（长篇大论）。

桌子Bought_Products

ID | Name | Customer_ID | Product_ID | Foo | Bar | Blarg
1  | test | 123         | 321        | NULL| 1   | NULL
2  | tast | 123         | 231        | 5   | NULL| 20
3  | tost | 456         | 321        | NULL| 3   | NULL

桌子Price

ID | Item | Product_ID | Cost
1  | Foo  | 321        | 10
2  | Bar  | 321        | 5
3  | BASE | 321        | 100
4  | BASE | 231        | 50
5  | Blarg| 231        | 0.5
6  | Foo  | 231        | 1

我需要这样的结果：

Customer | Products | Revenue
123      | 321, 231 | 170

对于客户 123，有 2 个产品：321 和 231，底价分别为 100 和 50；在 5 时加上 1 个“酒吧”项目；在 1 处加上 5 x 'Foo' 物品；和 20 个 0.5 的“Blarg”项目。

这就是我的开始：

select sum(p.cost) from price p
join Bought_Products bp on bp.product_id = p.product_ID
where p.item = 'BASE'

我需要将相关的 'Foo'、'Bar' 或 'Blarg' 值Bought_Products与各自相乘Price.Cost（将列标题与项目匹配）。

我还没有在 SQL 中使用过基本的数学函数。我不确定如何在 2 个表之间加入，然后将值相乘。使用子查询和后续连接？

我有 2 个表：Customer 和 Billing，Customer 包含一般详细信息（姓名、地址，您明白了），Billing 包含已计费的项目。

项目计费的结束日期是可变的（有些人是每月一次，其他人是 3 个月、6 个月、1 年等）。

在 Billing Item 中，客户可以拥有多个包裹 - 其中一些是将他们标记为经销商的包裹。我当前的 SQL 查询如下所示：

select customer.addresses_0_firstname AS AdminFName, 
       customer.addresses_0_lastname AS AdminLName, 
       customer.addresses_0_organisation AS CompanyName, 
       customer.addresses_0_address1 AS Address1, 
       customer.addresses_0_address2 AS Address2, 
       customer.addresses_0_city AS City, 
       customer.addresses_0_postcode AS Zip, 
       customer.addresses_0_country AS CountryID, 
       customer.addresses_0_email AS Email, 
       (CASE WHEN billing.lines_0_description like '%Package0%' 
             THEN 'False' 
             ELSE 'true' END) AS Reseller, 
       max(billing.end_date) 
from customer 
INNER JOIN billing on customer._id = billing.customer_$id 
where customer.name='example' 
  and (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%') 
group by customer.addresses_0_firstname, 
         customer.addresses_0_lastname, 
         customer.addresses_0_organisation, 
         customer.addresses_0_address1, 
         customer.addresses_0_address2, 
         customer.addresses_0_city, 
         customer.addresses_0_postcode, 
         customer.addresses_0_country, 
         customer.addresses_0_email, 
         billing.lines_0_description 

输出以下内容：

AdminFName  AdminLName  CompanyName Address1    Address2    City    Zip CountryID   Email           Reseller    (No column name)
Test User   Testing     123         test        Rd          test    0   Test        [email protected]   true        2012-03-23
Test User   Testing     123         test        Rd          test    0   Test        [email protected]   true        2012-01-23

这是所有用户数据（我们想要的）的列表，但加入了每个账单行，而不是选择具有最大值的 end_date。

我怀疑我必须做一个嵌套选择，但我不确定我在这里需要的逻辑。

建议？

我正在编写一个针对 SCOM 2012 OperationsManager 数据库运行的查询。SQL 实例是 Microsoft SQL Server 2012 - 11.0.5058.0 (X64)。

数据库中有一个名为Monitors. 数据库的模式可以在这里找到：链接到模式

我可以运行以下查询：

select monitorid, configurationxml 
from dbo.monitor 
where monitorid = '4960E39A-59C8-A2C2-99B1-59B73D73156F'

它返回这个：

monitorid                               configurationxml
4960E39A-59C8-A2C2-99B1-59B73D73156F    <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName><DiskLabel>$Target/Property[Type="Windows!Microsoft.Windows.LogicalDevice"]/DeviceID$</DiskLabel><CounterName>PercentFree</CounterName><IntervalSeconds>900</IntervalSeconds><NumSamples>4</NumSamples><SystemDriveWarningThreshold>10</SystemDriveWarningThreshold><SystemDriveErrorThreshold>5</SystemDriveErrorThreshold><NonSystemDriveWarningThreshold>10</NonSystemDriveWarningThreshold><NonSystemDriveErrorThreshold>5</NonSystemDriveErrorThreshold>

（因此列中的监视器 GUID 和 XMLconfigurationxml

然后我可以运行这个查询：

Select 
    D.C.value('IntervalSeconds[1]','varchar(4000)') CheckInterval,
    D.C.value('NumSamples[1]','varchar(4000)') SamplesBeforeDown,
    D.C.value('SystemDriveWarningThreshold[1]','varchar(4000)') SystemDriveWarningThreshold,
    D.C.value('CounterName[1]','varchar(4000)') Countertype
    FROM (Select n.c.query('.') as xmlquery
    from
     (Select cast(ConfigurationXML as xml) Recxml
     FROM [dbo].[monitor] mt)
    a Cross Apply Recxml.nodes('/') N(C)) r
    Cross Apply xmlquery.nodes('/') D(C)

哪个碎片（不确定这是否是正确的术语）XML 并允许我针对该列检索特定元素值：

CheckInterval   SamplesBeforeDown   SystemDriveWarningThreshold Countertype

          900                   4                            10 PercentFree

我遇到的问题是，我希望能够在该特定行中提取链接到 GUID 的特定元素值，这样我就可以获得我想要的 GUID 和 xpecific XML 元素值，如下所示：

monitorid                            CheckInterval  SamplesBeforeDown   SystemDriveWarningThreshold Countertype

4960E39A-59C8-A2C2-99B1-59B73D73156F           900                  4                            10 PercentFree

我遇到的主要问题是，当我粉碎 XML 时，它失去了对它来自的行的引用。

如何将 XML 内容链接到它来自的行？