delete * from table where id between 70 and 100
--или
delete * from table where id >= 70 and id =< 100
CREATE TABLE `TAB001_TYPE` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` TEXT NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `TAB002_FIELD` (
`id` INT NOT NULL AUTO_INCREMENT,
`t001_id` INT NOT NULL,
`field_name` TEXT NOT NULL,
`field_note` TEXT NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `TAB004_DOC_VALUE` (
`id` INT NOT NULL AUTO_INCREMENT,
`t003_id` INT NOT NULL,
`t002_id` INT NOT NULL,
`value` varchar NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `TAB003_DOCS` (
`id` INT NOT NULL AUTO_INCREMENT,
`t001_id` INT NOT NULL,
`doc_name` TEXT NOT NULL,
`doc_num` INT NOT NULL,
PRIMARY KEY (`id`)
);
ALTER TABLE `TAB002_FIELD` ADD CONSTRAINT `TAB002_FIELD_fk0` FOREIGN KEY (`t001_id`) REFERENCES `TAB001_TYPE`(`id`);
ALTER TABLE `TAB004_DOC_VALUE` ADD CONSTRAINT `TAB004_DOC_VALUE_fk0` FOREIGN KEY (`t003_id`) REFERENCES `TAB003_DOCS`(`id`);
ALTER TABLE `TAB004_DOC_VALUE` ADD CONSTRAINT `TAB004_DOC_VALUE_fk1` FOREIGN KEY (`t002_id`) REFERENCES `TAB002_FIELD`(`id`);
ALTER TABLE `TAB003_DOCS` ADD CONSTRAINT `TAB003_DOCS_fk0` FOREIGN KEY (`t001_id`) REFERENCES `TAB001_TYPE`(`id`);
SELECT USR1.NAME,
USR1.ID,
C.CNT,
D.EXCNT
FROM USERS USR1
LEFT JOIN (SELECT COUNT (*) AS CNT, OBJ.REALTOR_ID
FROM OBJECTS OBJ
GROUP BY REALTOR_ID) AS C ON USR.ID = C.REALTOR_ID
LEFT JOIN (SELECT COUNT (*) AS EXCNT, OBJ.REALTOR_ID
FROM OBJECTS OBJ WHERE OBJ.EXCLUSIVE = 1
GROUP BY REALTOR_ID) AS D ON USR.ID = D.REALTOR_ID;
WHERE MANAGER_ID IS NULL
ALTER TABLE `Услуги_предприятий`
ADD CONSTRAINT `<CONSTRAINT_NAME1>` FOREIGN KEY (`org_id`) REFERENCES `Предприятия` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `<CONSTRAINT_NAME2>` FOREIGN KEY (`service_id`) REFERENCES `Список_услуг` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
UPDATE my_tab2 as t2
JOIN (SELECT a.region,
a.host
FROM ftpup a,
(SELECT info,
type,
timestamp,
max(date) as time
FROM ftpup
GROUP BY type ) b
WHERE a.type = b.type
AND a.date = b.time
AND a.status <> '0' ) AS t1 ON t1.host = t2.host and t1.region = t2.host
SET t2.status = '2'
SELECT product_id FROM(
SELECT DISTINCT p.product_id, pd.name, p.model, p.quantity, p.price, p.sort_order, p.date_added , p.price as realprice
FROM oc_product p
LEFT JOIN oc_product_option_value pov ON pov.product_id = p.product_id
LEFT JOIN oc_product_description pd ON pd.product_id = p.product_id
LEFT JOIN oc_product_to_store p2s ON p2s.product_id = p.product_id
LEFT JOIN oc_product_to_category p2c ON p2c.product_id = p.product_id
LEFT JOIN oc_category_path cp ON cp.category_id = p2c.category_id
LEFT JOIN oc_product_attribute p2 ON p2.product_id = p.product_id
WHERE 1 AND cp.path_id IN (3558)
AND (
(p2.attribute_id = 12 AND p2a0.text LIKE '%ЛПО%') OR
(p2a1.attribute_id = 20 AND p2a1.text LIKE '%2х36 Вт%') OR
(p2a2.attribute_id = 22 AND p2a2.text LIKE '%накладны%') OR
(p2a3.attribute_id = 27 AND p2a3.text LIKE '%60 см%') OR
(p2a4.attribute_id = 29 AND p2a4.text LIKE '%T8%') OR
(p2a5.attribute_id = 30 AND p2a5.text LIKE '%220 В%')
)
AND pd.language_id = '1' AND p.status = '1' AND p2s.store_id = 0) as innertable
WHERE 1 ORDER BY sort_order ASC, LCASE(name) ASC LIMIT 0,20
SELECT `date`, COUNT(*) AS cnt, 'tot_user' as user FROM `l_sites` GROUP BY `date`
union all
SELECT `date`, COUNT(*) AS cnt, 'tot_user2' as user FROM `l_users` GROUP BY `date`
union all
SELECT `date`, COUNT(*) AS cnt, 'tot_user3' as user FROM `l_leads` GROUP BY `date`
SELECT case when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) = 2
then SUBSTRING(TEXT FROM LOCATE('"', TEXT)+1 FOR CHARACTER_LENGTH(TEXT) - LOCATE('"', TEXT)-1)
when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) = 3
then SUBSTRING(TEXT FROM LOCATE('"', TEXT,LOCATE('"', TEXT)+1)+1 FOR CHARACTER_LENGTH(TEXT)-LOCATE('"', TEXT,LOCATE('"', TEXT)+1)-1 )
when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) = 4
then SUBSTRING(TEXT FROM LOCATE('"', TEXT,LOCATE('"', TEXT)+1)+1 FOR CHARACTER_LENGTH(TEXT)-LOCATE('"', TEXT, LOCATE('"', TEXT,LOCATE('"', TEXT)+1)+1)-2)
else null
end
FROM mytable
select case when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) = 2
then SUBSTRING_INDEX(SUBSTRING_INDEX(TEXT, '"', 2), '"', -1)
when CHARACTER_LENGTH(TEXT) - CHARACTER_LENGTH(REPLACE(TEXT, '"', '')) > 2
then SUBSTRING_INDEX(SUBSTRING_INDEX(TEXT, '"', 3), '"', -1)
else null
end
FROM mytable
SELECT T.YEAR,
SUM (CASE WHEN T.W = 1 THEN 1 ELSE 0 END) AS MEN,
SUM (CASE WHEN T.W = 2 THEN 1 ELSE 0 END) AS WOMEN
FROM (SELECT (YEAR(CURRENT_DATE) - YEAR(F.DR)) DIV 10 * 10 AS YEAR, F.W
FROM DB_PERSON AS P LEFT JOIN DB_PERSON_FIO AS F ON P.ID = F.ID_PERSON
WHERE P.STAT > 0) AS T
GROUP BY T.YEAR
ORDER BY T.YEAR;
SELECT T.YEAR,
SUM (CASE WHEN T.W = 1 THEN 1 ELSE 0 END) AS MEN,
SUM (CASE WHEN T.W = 2 THEN 1 ELSE 0 END) AS WOMEN
FROM (SELECT (DATE_FORMAT(FROM_DAYS(TO_DAYS(CURRENT_DATE) - TO_DAYS(F.DR)), '%Y') + 0) DIV 10 * 10 AS YEAR,
F.W
FROM DB_PERSON AS P LEFT JOIN DB_PERSON_FIO AS F ON P.ID = F.ID_PERSON
WHERE P.STAT > 0) AS T
GROUP BY T.YEAR
ORDER BY T.YEAR;