SQL Oracle 中字符串列表中的条件计数 [英] conditional count in a list of strings in SQL Oracle
问题描述
我有几个项目的清单
column1 column2 所需的 col3 所需的 col4items1 汽车 汽车 1items2 car,car car 2items3 汽车、玩具、汽车玩具 1items4 汽车，玩具，玩具玩具 2items5 汽车、玩具、卡片玩具 1items6 玩具、卡片、卡片玩具 1items7 玩具、卡片、玩具玩具 2items8 汽车、卡片、玩具、卡片卡片 2items9 汽车，卡片，卡片卡片 2itrmd10 玩具、卡片、汽车玩具 1规则是，如果有汽车项目，那么在 col3 中应该是 car 并且在 col4 中的汽车数量如果列表中没有汽车项目或其他项目，则它应始终显示与汽车项目 + 此项目的计数不同的第一个项目.
例如
items3 列表中还有一个 item 为 car，在这种情况下，toy 是第一个，所以 toy 将显示为 1
items6 没有车所以玩具显示为第一个
items8 还有另外一个物品作为车出现，所以这次第一个是卡片，将显示为 2
items10 car 不必是列表中的第一项，但规则是相同的，列表中的另一个项为 car 然后计算第一个 = toy with count of 1
谢谢
规则是，如果有汽车项目，那么在 col3 中应该是 car 并且在 col4 中的汽车数量如果没有汽车项目或列表中存在另一个项目，那么它应该始终显示第一个不同形式的项目汽车项目 + 该项目的数量.
您可以使用:
SELECT t.*,REGEXP_COUNT(',' ||REPLACE(column2, ',', ',,' ) ||',',',' ||第 3 列 ||',') AS 第 4 列从 (选择第 1 列，第2列，案件当 ',' ||第 2 列 ||',' LIKE '%,car,%'然后车"ELSE REGEXP_SUBSTR( column2, '[^,]+' )END AS 第 3 列从表名) t对于您的示例数据:
CREATE TABLE table_name ( column1, column2 ) AS从 DUAL UNION ALL 中选择items1"、car"从 DUAL UNION ALL 中选择 'items2', 'car,car'从 DUAL UNION ALL 中选择 'items3', 'car,toy,car'从 DUAL UNION ALL 中选择 'items4', 'car,toy,toy'从 DUAL UNION ALL 中选择items5"、car,toy,cards"SELECT 'items6', 'toy,cards,cards' from DUAL UNION ALL从 DUAL UNION ALL 中选择 'items7', 'toy,cards,toy'SELECT 'items8', 'car,cards,toy,cards' from DUAL UNION ALLSELECT 'items9', 'car,cards,cards' from DUAL UNION ALL从 DUAL 中选择items10"、玩具、卡片、汽车"；输出:
<块引用><前>第 1 列 |第 2 栏 |第 3 栏 |第4栏:------ |:------------------ |:------ |------:项目1 |汽车 |汽车 |1项目2 |汽车，汽车|汽车 |2项目3 |汽车，玩具，汽车| 高分辨率照片| CLIPARTO汽车 |2项目4 |汽车、玩具、玩具| 高分辨率照片| CLIPARTO汽车 |1项目5 |汽车、玩具、卡片| 高分辨率照片| CLIPARTO汽车 |1项目6 |玩具、卡片、卡片| 高分辨率照片| CLIPARTO玩具|1项目7 |玩具、卡片、玩具| 高分辨率照片| CLIPARTO玩具|2项目8 |汽车、卡片、玩具、卡片| 高分辨率照片| CLIPARTO汽车 |1items9 |汽车、卡片、卡片| 高分辨率照片| CLIPARTO汽车 |1items10 |玩具、卡片、汽车|汽车 |1db<>fiddle 这里
如果您想要第一个非car 项目或car 如果只有car 项目，则:
SELECT column1,第2列，第3列，REGEXP_COUNT( double_delimited, ',' || column3 || ',' ) AS column4从 (选择 t.*,案件当 REPLACE(double_delimited, ',car,' ) 不是 NULLTHEN REGEXP_SUBSTR( REPLACE( double_delimited, ',car,' ), '[^,]+' )其他'汽车'END 为第 3 列从 (选择第 1 列，第2列，',' ||REPLACE(column2, ',', ',,' ) ||',' AS double_delimited从表名) t) t哪些输出:
<块引用><前>第 1 列 |第 2 栏 |第 3 栏 |第4栏:------ |:-------------------- |:------ |------:项目1 |汽车 |汽车 |1项目2 |汽车，汽车|汽车 |2项目3 |汽车，玩具，汽车| 高分辨率照片| CLIPARTO玩具|1项目4 |汽车、玩具、玩具| 高分辨率照片| CLIPARTO玩具|2项目5 |汽车、玩具、卡片| 高分辨率照片| CLIPARTO玩具|1项目6 |玩具、卡片、卡片| 高分辨率照片| CLIPARTO玩具|1项目7 |玩具、卡片、玩具| 高分辨率照片| CLIPARTO玩具|2项目8 |汽车、卡片、玩具、卡片| 高分辨率照片| CLIPARTO卡 |2items9 |汽车、卡片、卡片| 高分辨率照片| CLIPARTO卡 |2items10 |玩具、卡片、汽车|玩具|1db<>fiddle 这里
I have list of several items
column1 column2 desired col3 desired col4
items1 car car 1
items2 car,car car 2
items3 car,toy,car toy 1
items4 car,toy,toy toy 2
items5 car,toy,cards toy 1
items6 toy,cards,cards toy 1
items7 toy,cards,toy toy 2
items8 car,cards,toy,cards cards 2
items9 car,cards,cards cards 2
itrmd10 toy,cards,car toy 1
the rule is, if there is a car item then in col3 should come car and count of cars in col4 If there is no car item or another item is present in the list then it should always display the first one that differs form car item + the count of this item.
for example
items3 there is another item as car in the list, in this case toy is the first one, so toy with count 1 will be displayed
items6 there is no car so toy as the first one is displayed
items8 there are another items as car present so the first one is cards this time and will be displayed with count of 2
items10 car needn't be the first item in the list but the rule is the same,another item in the list as car then count the first one = toy with count of 1
thanks
the rule is, if there is a car item then in col3 should come car and count of cars in col4 If there is no car item or another item is present in the list then it should always display the first one that differs form car item + the count of this item.
You can use:
SELECT t.*,
REGEXP_COUNT(
',' || REPLACE( column2, ',', ',,' ) || ',',
',' || column3 || ','
) AS column4
FROM (
SELECT column1,
column2,
CASE
WHEN ',' || column2 || ',' LIKE '%,car,%'
THEN 'car'
ELSE REGEXP_SUBSTR( column2, '[^,]+' )
END AS column3
FROM table_name
) t
Which, for your sample data:
CREATE TABLE table_name ( column1, column2 ) AS
SELECT 'items1', 'car' FROM DUAL UNION ALL
SELECT 'items2', 'car,car' FROM DUAL UNION ALL
SELECT 'items3', 'car,toy,car' FROM DUAL UNION ALL
SELECT 'items4', 'car,toy,toy' FROM DUAL UNION ALL
SELECT 'items5', 'car,toy,cards' FROM DUAL UNION ALL
SELECT 'items6', 'toy,cards,cards' FROM DUAL UNION ALL
SELECT 'items7', 'toy,cards,toy' FROM DUAL UNION ALL
SELECT 'items8', 'car,cards,toy,cards' FROM DUAL UNION ALL
SELECT 'items9', 'car,cards,cards' FROM DUAL UNION ALL
SELECT 'items10', 'toy,cards,car' FROM DUAL;
Outputs:
COLUMN1 | COLUMN2 | COLUMN3 | COLUMN4 :------ | :------------------ | :------ | ------: items1 | car | car | 1 items2 | car,car | car | 2 items3 | car,toy,car | car | 2 items4 | car,toy,toy | car | 1 items5 | car,toy,cards | car | 1 items6 | toy,cards,cards | toy | 1 items7 | toy,cards,toy | toy | 2 items8 | car,cards,toy,cards | car | 1 items9 | car,cards,cards | car | 1 items10 | toy,cards,car | car | 1
db<>fiddle here
If you want the first non-car item or else car if only car items then:
SELECT column1,
column2,
column3,
REGEXP_COUNT( double_delimited, ',' || column3 || ',' ) AS column4
FROM (
SELECT t.*,
CASE
WHEN REPLACE( double_delimited, ',car,' ) IS NOT NULL
THEN REGEXP_SUBSTR( REPLACE( double_delimited, ',car,' ), '[^,]+' )
ELSE 'car'
END AS column3
FROM (
SELECT column1,
column2,
',' || REPLACE( column2, ',', ',,' ) || ',' AS double_delimited
FROM table_name
) t
) t
Which outputs:
COLUMN1 | COLUMN2 | COLUMN3 | COLUMN4 :------ | :------------------- | :------ | ------: items1 | car | car | 1 items2 | car,car | car | 2 items3 | car,toy,car | toy | 1 items4 | car,toy,toy | toy | 2 items5 | car,toy,cards | toy | 1 items6 | toy,cards,cards | toy | 1 items7 | toy,cards,toy | toy | 2 items8 | car,cards,toy,cards | cards | 2 items9 | car,cards,cards | cards | 2 items10 | toy,cards,car | toy | 1
db<>fiddle here
这篇关于SQL Oracle 中字符串列表中的条件计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
