sub countdir {
my $dir = shift @_;
my @files = < $dir >;
my $count = 0;
for my $file (@files) {
if (-d $file) {
$count += countdir("$file/*");
next;
}
open my $fd, '<', $file or die "cannot open file $file\n";
my @lines = <$fd>;
close $fd;
map { $count++ if ($_ !~ /^\s*$/) } @lines;
}
return $count;
}
print countdir('./*') . "\n";
UPDATE category SET number = 0;
SET @old_category = -1;
SET @num = -1;
UPDATE category AS c
INNER JOIN (
SELECT
t.article_id,
t.sort,
t.category_id,
@old_category,
@num := CASE
WHEN @old_category <> t.category_id THEN 0
ELSE @num + 1
END AS number,
@old_category := t.category_id
FROM (
SELECT
a.article_id,
a.sort,
c.category_id
FROM category AS c
INNER JOIN article AS a ON a.article_id = c.article_id
ORDER BY c.category_id, a.sort
) AS t
) AS t
ON t.article_id = c.article_id
SET c.number = t.number;
SELECT c.*, a.sort FROM category AS c
INNER JOIN article AS a ON a.article_id = c.article_id
ORDER BY c.category_id, a.sort
aricle_id | category_id | number | sort 5 | 1 | 0 | 4 3 | 1 | 1 | 7 4 | 2 | 0 | 2 1 | 2 | 1 | 3 2 | 2 | 2 | 5
CREATE DEFINER = 'root'@'localhost' TRIGGER `tab_before_ins_tr` BEFORE INSERT ON `tab`
FOR EACH ROW
BEGIN
SET NEW.Total_in_USD = NEW.quantity * NEW.USD;
END;
CREATE DEFINER = 'root'@'localhost' TRIGGER `tab_before_upd_tr` BEFORE UPDATE ON `tab`
FOR EACH ROW
BEGIN
SET NEW.Total_in_USD = NEW.quantity * NEW.USD;
END;
SELECT
t.city_id,
COALESCE(cru.lang_id, cen.lang_id) AS lang_id,
COALESCE(cru.`name`, cen.`name`) AS `name`
FROM (
SELECT city_id FROM city
WHERE lang_id IN('ru', 'en')
GROUP BY city_id
) AS t
LEFT JOIN city AS cru ON cru.city_id = t.city_id AND cru.lang_id = 'ru'
LEFT JOIN city AS cen ON cen.city_id = t.city_id AND cen.lang_id = 'en'
INSERT INTO tabl2 (login, top)
SELECT * FROM (SELECT login, COUNT(*) AS cnt FROM tabl1 GROUP BY login) AS t1
ON DUPLICATE KEY UPDATE top = t1.cnt;
SELECT DISTINCT t.nid, f.* FROM (
SELECT
id,
parentId,
isMain,
@nid := CASE
WHEN @parentId <> parentId
THEN id
ELSE @nid
END AS nid,
@parentId := parentId AS t2
FROM hfeed
ORDER BY parentID, isMain DESC
) AS t
INNER JOIN hfeed AS f ON f.id = t.nid
SELECT email, `status` FROM (
SELECT email, `status`, COUNT(*) AS cnt
FROM mails
GROUP BY email
HAVING `status` IN ('status1', 'status2')
) AS subq WHERE cnt = 1;