我希望对以下 SQL 场景有所帮助
当前状态：
对于 med_id、casemgr_id、casemgr_clnt_id 的特定组合，应相应地反映状态：
期望输出：
在第一种情况下，对于 med_id=98410、casemgr_id=12345、casemgr_clnt_id=67891，日期（第 1 行）有效，但对于相同的行，如果日期在 6 个月内，则状态应为无效。在第二种情况下，对于 med_id=91956、casemgr_id=99012、casemgr_clnt_id=87567，日期（第 1 行）有效，但对于第二行，由于日期大于 6 个月，因此状态有效。总之，对于 med_id、casemgr_id 和 casemgr_clntid 的重复行，第一行的状态将有效，但对于后续行，如果日期 > 6 个月，则状态有效，否则如果少于 6 个月，则状态无效。
我如何在 SQL 中实现这一点？请帮忙！
这是我的数据样本
select * from (
select 'A' as POD, 164 as result from dual union all
select 'A' as POD, 3 as result from dual union all
select 'A' as POD, 2 as result from dual union all
select 'B' as POD, 409 as result from dual union all
select 'B' as POD, 128 as result from dual union all
select 'B' as POD, 5 as result from dual union all
select 'C' as POD, 12391 as result from dual union all
select 'C' as POD, 624 as result from dual union all
select 'C' as POD, 405 as result from dual union all
select 'C' as POD, 26 as result from dual union all
select 'C' as POD, 3 as result from dual union all
select 'C' as POD, 2 as result from dual
)
| 荚 | 结果 |
|---|---|
| 一个 | 164 |
| 一个 | 3 |
| 一个 | 2 |
| 乙 | 409 |
| 乙 | 128 |
| 乙 | 5 |
| 碳 | 12391 |
| 碳 | 624 |
| 碳 | 405 |
| 碳 | 二十六 |
我想要的是按照首次计数最高的组对它们进行排序：
| 荚 | 结果 |
|---|---|
| 碳 | 12391 |
| 碳 | 624 |
| 碳 | 405 |
| 碳 | 二十六 |
| 乙 | 409 |
| 乙 | 128 |
| 乙 | 5 |
| 一个 | 164 |
| 一个 | 3 |
| 一个 | 2 |
我甚至不知道如何用 SQL 来表达这个
Cresult在第一行中具有最高值，然后B具有第二高值A
给定如下数据表：
| 一个 | b | 丙 | d | 埃 |
|---|---|---|---|---|
| 1 | 测试 | 9 | 时长 | 2024-10-22 08:00:00.000 |
| 1 | 测试 | 9 | 升 | 2024-10-23 08:00:00.000 |
| 1 | 测试 | 9 | 问 | 2024-10-22 08:00:00.000 |
我想按 a、b、c 列对数据进行分组，并显示 d 列中具有最新日期的值（e 列）。
因此，我希望得到一行这样的数据：
| 一个 | b | 丙 | d |
|---|---|---|---|
| 1 | 测试 | 9 | 升 |
我本来希望有像下面这样简单的东西，例如“last()”，但据我所知，没有这么简单的东西？
SELECT
a, b, c,
last(d)
FROM
dbo.items
GROUP BY
a, b, c
我能找到的唯一接近我想要的例子是LAST_VALUE OVER PARTITION它在 group by 中不起作用
LAST_VALUE(d) OVER (PARTITION BY d ORDER BY e) AS d
我知道类似的事情也可以访问不在分组依据中的内容，比如如果 b 想要在分组依据中，我仍然可以访问STRING_AGG所有的值，像这样
STRING_AGG(b, ',') AS b
并得到“test,test,test”作为值
Processed如何使用最少的 SQLite 3 查询获取表中没有关联的未处理行？
嵌套查询可以正常工作。
Main_Table列：ID
Processed列：（record1_id主表的 id 的外键），record2_id（的 id 的外键Main_Table）
我想要获取两个Main_table尚未一起处理的记录。
然后我处理这两条记录并插入一个条目，其中Processed记录1的id为record1_id，记录2的id为record2_id。
然后，当我选择接下来的两个记录Main_Table进行处理时，我不想要已经处理过的记录。
CREATE USER test_user WITH PASSWORD 'test_password'
密码值需要单引号，用户名不需要。我只是想知道为什么会这样？两者看起来都像输入参数（字符串），但处理方式不同。
我想创建一个 SQL 存储过程来检查登录时的密码。这应该是一个非常常见的问题，但是我找不到任何答案来回答我的具体问题，即关于数据类型，或者类似的东西。我正在使用 MySQL 数据库，但我不确定我具体使用的是哪种类型的 SQL。这是我的 SQL：
CREATE PROCEDURE SelectAllUsers @Password varchar(12)
AS
SELECT * FROM Logins WHERE Password = @Password
GO;
我有以下查询中的以下数据样本，其中我有两个UNIONed 查询。我想从总体查询中排除ITEM_NUMBER在第二个查询中存在与SUBINVENTORY_CODE第一个查询中相同的记录，并且INV_LOCATOR第二个查询中的记录的值为“AAAAAAA”。我曾尝试通过使用分析窗口函数来实现这一点ROW_NUMBER，但我承认我没有看到我需要如何更改它以ROW_NUMBER适当地设置值，然后在最外层的查询中对其进行过滤。
这是我基于以下查询的当前结果：
ITEM_NUMBER SUBINVENTORY_CODE INV_LOCATOR QUERY IVT_SEQNUM PRIORITY OVERALL_PRIORITY
12923 41OPR Z-B-01-02-C 1st Query 1 1 1
12923 41OPR W-130-00-00-00 1st Query 2 1 2
12923 11OPR AAAAAAA 2nd Query 1 2 1
12923 41OPR AAAAAAA 2nd Query 1 2 1
期望结果：
ITEM_NUMBER SUBINVENTORY_CODE INV_LOCATOR QUERY IVT_SEQNUM PRIORITY OVERALL_PRIORITY
12923 41OPR Z-B-01-02-C 1st Query 1 1 1
12923 41OPR W-130-00-00-00 1st Query 2 1 2
12923 11OPR AAAAAAA 2nd Query 1 2 1
正如您在所需结果中看到的，我们从“第二个查询”行中排除了最后一条记录，因为它包含ANDINV_LOCATOR的值，并且此相同和组合已经存在于“第一个查询”的结果集中AAAAAAA ITEM_NUMBERSUBINVENTORY_CODE
SELECT * FROM
(SELECT item_number, subinventory_code, INV_LOCATOR, QUERY, ivt_seqnum, priority, ROW_NUMBER() OVER (PARTITION BY priority, item_number,
subinventory_code ORDER BY priority DESC, priority) Overall_Priority
FROM
(SELECT ESI.item_number, IOQD.subinventory_code SUBINVENTORY_CODE,
ROW_NUMBER() OVER (PARTITION BY ESI.item_number , IOQD.subinventory_code
ORDER BY ESI.item_number, IOQD.subinventory_code ) ivt_seqnum
,LOC.SEGMENT1||'-'||LOC.SEGMENT2||'-'||LOC.SEGMENT3||'-'||LOC.SEGMENT4||'-'||LOC.SEGMENT5 INV_LOCATOR, 1 as priority, '1st Query' AS QUERY
FROM egp_system_items ESI, inv_onhand_quantities_detail IOQD, inv_item_locations LOC
WHERE ESI.INVENTORY_ITEM_ID = 100000110904412
AND ioqd.subinventory_code = '41OPR'
AND IOQD.inventory_item_id = ESI.inventory_item_id
AND LOC.inventory_location_id = ioqd.locator_id
UNION ALL
SELECT ESI.item_number, decode(substr(IOQD.secondary_inventory,1,3),
,'41O','41OPR'
,IOQD.secondary_inventory) subinventory_code,
ROW_NUMBER() OVER (PARTITION BY ESI.item_number , IOQD.secondary_inventory
ORDER BY ESI.item_number, IOQD.secondary_inventory ) ivt_seqnum ,
case when
(SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
FROM inv_item_loc_defaults iild,
inv_item_locations iil
WHERE iild.inventory_item_id = esi.inventory_item_id
AND iild.subinventory_code = IOQD.secondary_inventory
AND iild.locator_id = iil.inventory_location_id
AND rownum= 1 and length(Segment1) >= 1 ) is not null
then (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
FROM inv_item_loc_defaults iild,
inv_item_locations iil
WHERE iild.inventory_item_id = esi.inventory_item_id
AND iild.subinventory_code = IOQD.secondary_inventory
AND iild.locator_id = iil.inventory_location_id
AND rownum= 1 and length(Segment1) >= 1 )
Else 'AAAAAAA'
END as INV_LOCATOR , 2 as priority , '2nd Query' AS QUERY
FROM egp_system_items ESI, INV_ITEM_SUB_INVENTORIES IOQD
WHERE IOQD.inventory_item_id = ESI.inventory_item_id
AND ESI.INVENTORY_ITEM_ID = 100000110904412
) --WHERE (priority <> 2 AND INV_LOCATOR <> 'AAAAAAA')
)
编辑
我添加了名为 cnt_first_query 的附加分析函数，并尝试在外部查询中对其进行过滤，但现在它仅获取 1 行，即下面的第 3 行“11OPR”和“AAAAAAA”
SELECT * FROM
(SELECT item_number, subinventory_code, INV_LOCATOR, QUERY, ivt_seqnum, priority, ROW_NUMBER() OVER (PARTITION BY priority, subinventory_code ORDER BY priority, subinventory_code ) Overall_Priority
,COUNT(CASE WHEN priority = 1 THEN 1 END) OVER (PARTITION BY item_number, subinventory_code) as cnt_first_query
FROM
(SELECT ESI.item_number, IOQD.subinventory_code SUBINVENTORY_CODE,
ROW_NUMBER() OVER (PARTITION BY ESI.item_number , IOQD.subinventory_code
ORDER BY ESI.item_number, IOQD.subinventory_code ) ivt_seqnum
,LOC.SEGMENT1||'-'||LOC.SEGMENT2||'-'||LOC.SEGMENT3||'-'||LOC.SEGMENT4||'-'||LOC.SEGMENT5 INV_LOCATOR, 1 as priority, '1st Query' AS QUERY --, ioqd.locator_id
FROM egp_system_items ESI, inv_onhand_quantities_detail IOQD, inv_item_locations LOC
WHERE ESI.INVENTORY_ITEM_ID = 100000110904412
AND ioqd.subinventory_code = '41OPR'
AND IOQD.inventory_item_id = ESI.inventory_item_id
and IOQD.organization_id = esi.organization_id
AND LOC.inventory_location_id = ioqd.locator_id
UNION ALL
SELECT ESI.item_number, decode(substr(IOQD.secondary_inventory,1,3),
'11C','11CCL'
,'11O','11OPR'
,'18O','18OPR'
,'41S','24000'
,'41O','41OPR'
,'60O','60OPR'
,'70O','70OPR'
,'70C','70CCL'
,'70M', '70OPR'
,IOQD.secondary_inventory) subinventory_code,
ROW_NUMBER() OVER (PARTITION BY ESI.item_number , IOQD.secondary_inventory
ORDER BY ESI.item_number, IOQD.secondary_inventory ) ivt_seqnum ,
case when
(SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
FROM inv_item_loc_defaults iild,
inv_item_locations iil
WHERE iild.inventory_item_id = esi.inventory_item_id
AND iild.subinventory_code = IOQD.secondary_inventory
AND iild.locator_id = iil.inventory_location_id
AND rownum= 1 and length(Segment1) >= 1 ) is not null
then (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
FROM inv_item_loc_defaults iild,
inv_item_locations iil
WHERE iild.inventory_item_id = esi.inventory_item_id
AND iild.subinventory_code = IOQD.secondary_inventory
AND iild.locator_id = iil.inventory_location_id
AND rownum= 1 and length(Segment1) >= 1 )
when (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
FROM inv_secondary_locators iild,
inv_item_locations iil
WHERE iild.inventory_item_id = esi.inventory_item_id
AND iild.subinventory_code = IOQD.secondary_inventory
AND iild.SECONDARY_LOCATOR= iil.inventory_location_id
AND rownum = 1 and length(Segment1) >= 1 ) is not null
Then (SELECT iil.SEGMENT1||'-'||iil.SEGMENT2||'-'||iil.SEGMENT3||'-'||iil.SEGMENT4||'-'||iil.SEGMENT5
FROM inv_secondary_locators iild,
inv_item_locations iil
WHERE iild.inventory_item_id = esi.inventory_item_id
AND iild.subinventory_code = IOQD.secondary_inventory
AND iild.SECONDARY_LOCATOR= iil.inventory_location_id
AND rownum = 1 and length(Segment1) >= 1 )
Else 'AAAAAAA'
END as INV_LOCATOR , 2 as priority , '2nd Query' AS QUERY
FROM egp_system_items ESI, INV_ITEM_SUB_INVENTORIES IOQD --, inv_item_loc_defaults iild, inv_item_locations iil
WHERE IOQD.inventory_item_id = ESI.inventory_item_id
AND IOQD.organization_id = ESI.organization_id
AND ESI.INVENTORY_ITEM_ID = 100000110904412
--AND iild.inventory_item_id = esi.inventory_item_id
--AND iild.subinventory_code = IOQD.secondary_inventory
--AND iild.locator_id = iil.inventory_location_id
) --WHERE (priority <> 2 AND INV_LOCATOR <> 'AAAAAAA')
) --WHERE (INV_LOCATOR <> 'AAAAAAA' AND OVERALL_PRIORITY <=1)
WHERE NOT cnt_first_query > 0 AND INV_LOCATOR = 'AAAAAAA'
我有一个跟踪用户活动的数据集，我需要识别满足特定条件的连续行。表结构和示例数据如下：
| 用户身份 | 活动 | 时间戳 |
|---|---|---|
| 1 | 登录 | 2023-11-01 08:00:00 |
| 1 | 页面视图 | 2023-11-01 08:10:00 |
| 1 | 登录 | 2023-11-01 08:15:00 |
| 1 | 登出 | 2023-11-01 08:20:00 |
| 2 | 登录 | 2023-11-01 09:00:00 |
| 2 | 登录 | 2023-11-01 09:05:00 |
| 2 | 页面视图 | 2023-11-01 09:10:00 |
我想识别同一用户的连续行具有相同活动及其时间戳的情况。例如：
对于 UserID = 2，有两个连续的登录活动。像这样。
| 用户身份 | 活动 | 开始时间戳 | 结束时间戳 |
|---|---|---|---|
| 2 | 登录 | 2023-11-01 09:00:00 | 2023-11-01 09:05:00 |
我尝试使用自连接将每一行与前一行进行比较，但对于大型数据集来说，这会导致计算成本过高。我也尝试使用LAG和LEAD函数，但无法有效地对连续行进行分组。
我有一个场景，我只想根据 TID 列中的列值从一个组中取出一行。例如，我在表中的关键列是 SRN、GIO 和 FID。现在有另一列 TID，现在在一个组中，当 case 语句中给出的两个 TIDS 存在于组中时，我需要取一个等级为 1（计算）的记录，否则我们可以取另一条记录。还请注意，有些记录不属于此条件，也应该作为输出。给出示例记录，以及下面的预期输出
输入数据
| 顺应性 | 吉奥 | 吉奥 | 工时信息 |
|---|---|---|---|
| ASD123 | 1 | 100 | 218 |
| ASD123 | 1 | 100 | 303 |
| ASD123 | 1 | 100 | 454 |
| ASD123 | 1 | 100 | 678 |
| DEF234 | 0 | 6566 | 2222 |
| DEF234 | 0 | 6566 | 307 |
| DEF234 | 0 | 6566 | 2宫内节育器 |
| WER453 | 1 | 8123 | 308 |
| WER453 | 1 | 8123 | 2wER |
| WER453 | 1 | 8123 | 56TY |
我编写了如下查询，但是由于存在多个案例条件，因此会对每个条件进行全表扫描，这会导致性能下降，因为表中有很多记录。那么有没有更好的方法来重写这个查询。
sel case when TID in('218','303')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='218' then 1 else 2 END) end COL_3 ,
case when TID in('2222','307')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='2222' then 1 else 2 END) end COL_4 ,
case when TID in('2202','308')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='2202' then 1 else 2 END) end COL_5 ,
case when TID in('2220','305')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='2220' then 1 else 2 END) end COL_6 ,
case when TID in('2211','311')
then RANK() OVER (PARTITION BY SRN, GIO,FID ORDER BY CASE when TID ='2211' then 1 else 2 END) end COL_7
from table where
((COL_3 = 1 or COL_4 = 1 OR COL_5=1 OR COL_6=1 OR COL_7=1)
OR
(COL_3 is null and COL_4 is null and COL_5 is null and COL_6 is null and COL_7 is null)
)
预期输出
| 顺应性 | 吉奥 | 吉奥 | 工时信息 |
|---|---|---|---|
| ASD123 | 1 | 100 | 218 |
| ASD123 | 1 | 100 | 454 |
| ASD123 | 1 | 100 | 678 |
| DEF234 | 0 | 6566 | 2222 |
| DEF234 | 0 | 6566 | 2宫内节育器 |
| WER453 | 1 | 8123 | 308 |
| WER453 | 1 | 8123 | 2wER |
| WER453 | 1 | 8123 | 56TY |
我想创建一个列numberofimage。列中的每个单元格将显示该列numberofimage中的图像数量addimage
列中每个单元格的格式addimage如下：
[{"file":"vstorage.com\/sohoa\/han01\/\/123.jpg","name":"123.jpg","type":"image\/jpeg","size":239709},{"file":"storage.com\/sohoa\/han01\/\/456.jpg","name":"456.png","type":"image\/png","size":1030361}]
我会像这样计算每个细胞的数量，但结果却错误：
SELECT * FROM `surveytemplate1`
WHERE addphoto LIKE '%.gif%'
OR addphoto LIKE '"Type:"image\/jpeg"'
OR addphoto LIKE '"Type":"image\/png"'
OR addphoto LIKE '"Type":"image\/jpg"'
因此，在上面的例子中，有 2 张图片，因为有 2 个单词：“ Type:"image\/jpeg”和“ Type":"image\/png”
那么我应该使用什么公式来实现我想要的结果？MySQL 是 v5.5
