SELECT * FROM cmsContent C
INNER JOIN cmsContentType CT ON C.contentType = CT.nodeId
INNER JOIN umbracoNode N ON C.nodeId = N.id
WHERE CT.alias = ‘yourDocumentTypeAliasHere’

CREATE TABLE `test_null` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `testfield` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;


# 删除为空的字段
delete from test_null where isnull(testfield);

SELECT pp_mfDev.positions_price_archive.pa_num, xignite.MAXDATA.TICKER as maxticker 
FROM pp_mfDev.positions_price_archive 
LEFT JOIN xignite.MAXDATA 
ON xignite.MAXDATA.TICKER = pp_mfDev.positions_price_archive.ticker 
WHERE pp_mfDev.positions_price_archive.pa_date = "2017-06-01";

// same query using table aliases
SELECT pp.pa_num, x.TICKER as maxticker 
FROM pp_mfDev.positions_price_archive pp
LEFT JOIN xignite.MAXDATA x
ON x.TICKER = pp.ticker 
WHERE pp.pa_date = "2017-06-01";

UPDATE line_items 
LEFT JOIN accounts ON accounts.accountnum = line_items.accountnum
SET line_items.processed = "2013-08-11" 
WHERE line_items.batch_num = 18 AND accounts.clinum = 6666;

SELECT stel.SYM, stel.RT1MONTH, val10kst.VAL10K, val10kst.VAL10KID, val10kst.UPDATED
FROM stel
LEFT JOIN val10kst on val10kst.TICKER=stel.SYM
WHERE val10kst.VALDATE=(SELECT MAX(val10kst.VALDATE) FROM val10kst) AND stel.SYM !=''

Use Master
Go
RESTORE DATABASE NewDBNameGoesHere
FROM DISK = 'C:\file.bak'

-- START 22192 / 07.08 12'936 / 09.08 9'110 / 12.08 8'642 / 13.08 8'194 / 14.08 8'027 / 15.08 6'161 / 19.08 6'183 / 21.08 5'529 / 23.08 5'490  / 28.08 5'580 / 30.08 5'642 / 16.09 939
SELECT a.id AS offeneUnklassifizierte

FROM Artikel                      a
  JOIN Artikelvariante            av    ON a.id  = av.artikel_id
  JOIN ArtikelvarianteInLagerort  avil  ON av.id = avil.artikelvariante_id
  LEFT JOIN FarbfamilieInArtikel  fia   ON a.id  = fia.artikel_id
  
WHERE Klassifizierung IS NULL OR (Sexcode IS NULL AND Klassifizierung NOT IN ('BEAUTY', 'TOYS'))
    AND a.id NOT IN (SELECT aik.artikel_id from ArtikelInKampagne aik where aik.kampagne_id IN (255, 256, 257))
GROUP BY a.id

HAVING IFNULL(SUM(avil.bestand), 0) > 0

ORDER BY a.id;

-- START 57'123 / 07.08. 52'498 / 09.08 52'430 / 12.08 47'685 / 13.08 46'952 /14.08 43'528 / 15.08 41'143 / 19.08 36'316 / 21.08 33'996 / 23.08 32'603 / 28.08 29'993 /30.08 29'498 / 16.09 19'645
SELECT COUNT( * ) AS umklassifizierung
FROM Artikel
WHERE klassifizierung
IN (
'OBERTEILE_LEICHT',  'OBERTEILE_SCHWER',  'HOME_FURNITURE',  'HOME_TEXTIL',  'HOME_GOURMET',  'HOME_ACCESSOIRES'
);

-- START 20'627 / 07.08. 20'869 / 09.08 20'511 / 12.08 20'517 /13.08 20'535 / 14.08 20'535 / 15.08 20'358 / 19.08 19'366 / 21.08 19'375 / 23.08 19'243 / 28.08 19'229 / 28.08 19'159 / 16.09 18'887
SELECT COUNT( * ) AS zuKontrollierendeArtikel 
FROM Artikel
WHERE klassifizierung
IN (
'ACCESSOIRES',  'SCHUHE'
);

SELECT x3.`category_child_id`, cat.category_name
FROM jos_pl_vm_category cat
LEFT JOIN  `jos_pl_vm_category_xref` x3 ON x3.`category_child_id` = cat.category_id
LEFT JOIN `jos_pl_vm_category_xref` x2 ON x3.`category_parent_id` = x2.`category_child_id`
WHERE x2.`category_parent_id` = 3019 #HP

SELECT COUNT(name) AS num,name FROM xd_auto_category where level=2 and typename="rank" GROUP BY name having(num)>1 ORDER BY num DESC 

#3016-3054
select round(rand()*99%38)+3016

#3592-3595
set sql_notes = 0;
update xd_guide set catid= round(rand()*99%3)+3592 where catid=0 order by rand() limit 10;
select count(itemid) from xd_guide where catid=0;

#10-29 phpcms v9
update mk_info set catid= round(rand()*99%29)+10 order by rand() limit 10;

UPDATE kl_question
SET title = SUBSTR(title, 1, CHAR_LENGTH(title) - 1)
WHERE culture = 'el_GR'
AND title LIKE '%;';