(SELECT DISTINCT(Name) AS storekeeperName FROM User, Waybill WHERE (Waybill.loginStorekeeper = User.login))
SELECT
Waybill.Id, Waybill.IMO, Vessel.Name, Waybill.loginManager, User.Name, Waybill.loginStorekeeper,
(SELECT DISTINCT(Name) AS storekeeperName FROM User, Waybill WHERE (Waybill.loginStorekeeper = User.login)) AS storekeeperName,
Waybill.Date
FROM Waybill, Vessel, User
WHERE (Waybill.IMO = Vessel.IMO)
AND (Waybill.loginManager = User.Login)
AND (Waybill.loginManager = User.Login)
ORDER BY Waybill.Id
$sql = "SELECT * FROM tbl WHERE replace(replace(replace(replace(phone,'(',''),')',''),' ',''),'-','') LIKE concat(replace(replace(replace(replace('".$num."','(',''),')',''),' ',''),'-',''), '%')";
Не совсем понимаю как это реализовать с помощь полнотекстового поиска.
^\+?7[ -]?\(?\d{3}\)?[ -]?\d{3}[ -]?\d{2}[ -]?\d{2}$
SELECT `zadachi`.id AS id_zadacha,`zadachi`.*,`users`.*
FROM `zadachi`
INNER JOIN `users` ON `users`.id_1c = `zadachi`.autor
WHERE `zadachi`.komy = '$id' OR (`zadachi`.autor = '$id' AND `zadachi`.komy = '$id')
ORDER BY `zadachi`.id DESC
а: Группа новых задач...
б: Группа просроченных задач...
в: Группа поставленных задач кому то...
SELECT 'NEW' as group, .... FROM ...
UNION ALL
SELECT 'EXPIRED' as group, ... FROM ...
UNION ALL
SELECT 'OTHER' as group, ... FROM ...
-- выбираем несуществующую запись
SELECT pt.pid, t.id, t.text FROM (SELECT 1 AS pid) pt
FULL OUTER JOIN (
SELECT 1 AS pid, tt.*
FROM tbl tt
-- условие запроса:
WHERE tt.id = 10
) AS t ON pt.pid = t.pid
;
-- выбираем несуществующую запись
SELECT pt.pid, t.id, t.text FROM (SELECT 1 AS pid) pt
LEFT OUTER JOIN (
SELECT 1 AS pid, tt.*
FROM tbl tt
-- условие запроса:
WHERE tt.id = 10
) AS t ON pt.pid = t.pid
;
SELECT id, text FROM tbl
WHERE id = 10
UNION
SELECT NULL, NULL
;
SELECT
DECODE(NVL(an.id,0), 0,ao.id, an.id) AS id,
DECODE(NVL(an.id,0), 0,ao.dt_beg, an.dt_beg) AS dt_beg,
DECODE(NVL(an.id,0), 0,ao.dt_end, an.dt_end) AS dt_end
FROM
(.....) ao
FULL OUTER JOIN (.....) an ON ao.id = an.id
WHERE (an.id is null OR ao.id is null)
AND (ao.id = _NUMBER_ OR an.id = _NUMBER_)
;
set @i = 0;
update `grey_csgo_gifts_list`
set id = @i := @i + 1;
alter table `grey_csgo_gifts_list` modify `id` int(11) auto_increment primary key;
select
replace(
text
,CONCAT('</table>', CHAR(10), CHAR(10),'</li>',CHAR(10),'</ul>')
, '</table>'
) as text
from tbl;
set @a = CONCAT('</table>', CHAR(10), CHAR(10),'</li>',CHAR(10),'</ul>');
update tbl
set text = replace(text, @a, '</table>');
</table>
или в тэгах есть побельные символы (пробел, табуляция и пр.) и/или "белые пробелы".(но нам то это неизвестно, данные у ВАС)
update tbl
set text = REGEXP_REPLACE(
text
,'<[[:blank:][:space:]]*\/table>[[:blank:][:space:]]+<[[:blank:][:space:]]*\/li>[[:blank:][:space:]]+<[[:blank:][:space:]]*\/ul>[[:blank:][:space:]]*'
,'</table>'
)
SELECT
`ID`
, `post_title`
, pv30.cnt_month
, COALESCE(pv7.cnt_week, 0) cnt_week
, COALESCE(pv1.cnt_day, 0) cnt_day
FROM `wp_posts` AS p
INNER JOIN (
SELECT id, count(id) cnt_month
FROM `wp_post_views` pv
WHERE pv.time >= CURDATE() - INTERVAL 30 DAY
GROUP BY pv.id
) AS pv30
ON pv30.id = p.id
LEFT JOIN (
SELECT id, count(id) cnt_week
FROM `wp_post_views` pv
WHERE pv.time >= CURDATE() - INTERVAL 7 DAY
GROUP BY pv.id
) AS pv7
ON pv7.id = p.id
LEFT JOIN (
SELECT id, count(id) cnt_day
FROM `wp_post_views` pv
WHERE pv.time >= CURDATE() - INTERVAL 1 DAY
GROUP BY pv.id
) AS pv1
ON pv1.id = p.id
ORDER BY pv30.cnt_month DESC, pv7.cnt_week DESC, pv1.cnt_day DESC
LIMIT 10;
BULK INSERT LR1.dbo.Company
FROM '\\Mac\Home\Descktop\DB\data\Company.txt'
WITH (FORMAT = 'CSV'
, FIRSTROW=1
, FIELDTERMINATOR = ';'
, ROWTERMINATOR = '0x0a');
7-Zip 19.00 (x86) : Copyright (c) 1999-2018 Igor Pavlov : 2019-02-21
Usage: 7z [...] [...] [@listfile]
a : Add files to archive
b : Benchmark
d : Delete files from archive
e : Extract files from archive (without using directory names)
h : Calculate hash values for files
i : Show information about supported formats
l : List contents of archive
rn : Rename files in archive
t : Test integrity of archive
u : Update files to archive
x : eXtract files with full paths
-- : Stop switches and @listfile parsing
-ai[r[-|0]]{@listfile|!wildcard} : Include archives
-ax[r[-|0]]{@listfile|!wildcard} : eXclude archives
-ao{a|s|t|u} : set Overwrite mode
-an : disable archive_name field
-bb[0-3] : set output log level
-bd : disable progress indicator
-bs{o|e|p}{0|1|2} : set output stream for output/error/progress line
-bt : show execution time statistics
-i[r[-|0]]{@listfile|!wildcard} : Include filenames
-m{Parameters} : set compression Method
-mmt[N] : set number of CPU threads
-mx[N] : set compression level: -mx1 (fastest) ... -mx9 (ultra)
-o{Directory} : set Output directory
-p{Password} : set Password
-r[-|0] : Recurse subdirectories
-sa{a|e|s} : set Archive name mode
-scc{UTF-8|WIN|DOS} : set charset for for console input/output
-scs{UTF-8|UTF-16LE|UTF-16BE|WIN|DOS|{id}} : set charset for list files
-scrc[CRC32|CRC64|SHA1|SHA256|*] : set hash function for x, e, h commands
-sdel : delete files after compression
-seml[.] : send archive by email
-sfx[{name}] : Create SFX archive
-si[{name}] : read data from stdin
-slp : set Large Pages mode
-slt : show technical information for l (List) command
-snh : store hard links as links
-snl : store symbolic links as links
-sni : store NT security information
-sns[-] : store NTFS alternate streams
-so : write data to stdout
-spd : disable wildcard matching for file names
-spe : eliminate duplication of root folder for extract command
-spf : use fully qualified file paths
-ssc[-] : set sensitive case mode
-sse : stop archive creating, if it can't open some input file
-ssw : compress shared files
-stl : set archive timestamp from the most recently modified file
-stm{HexMask} : set CPU thread affinity mask (hexadecimal number)
-stx{Type} : exclude archive type
-t{Type} : Set type of archive
-u[-][p#][q#][r#][x#][y#][z#][!newArchiveName] : Update options
-v{Size}[b|k|m|g] : Create volumes
-w[{path}] : assign Work directory. Empty path means a temporary directory
-x[r[-|0]]{@listfile|!wildcard} : eXclude filenames
-y : assume Yes on all queries
"C:\Program Files (x86)\7-Zip\7z.exe" x -so G:\FIAS\fias_07.10.19.zip sql_file1.sql | G:\MySqlWork\mysql.exe -uroot -p 4830 newBase
"C:\Program Files (x86)\7-Zip\7z.exe" x -so G:\FIAS\fias_07.10.19.zip sql_file2.sql | G:\MySqlWork\mysql.exe -uroot -p 4830 newBase
SELECT
key,
count(DECODE(value, 'tino',1, NULL)) AS tino,
count(DECODE(value, 'tipo',1, NULL)) AS tipo,
count(DECODE(value, 'tiko',1, NULL)) AS tiko
FROM table1
GROUP BY key
SELECT
t2.b1,
t2.b2,
t2.b3,
ARRAY_AGG(t2.value)
FROM table2 t2
WHERE EXISTS (
SELECT 1
FROM table1 t1
WHERE t1.a1 = t2.b1
AND t1.a2 = t2.b2
AND t1.a3 = t2.b3
)
GROUP BY t2.b1, t2.b2, t2.b3
Подскажите пожалуйста, как в колонке array_agg получить уникальные значения (в виде массива)